Share via

How to change identity column in Clustered Index from int to big int for a 42gb table

Grossnickle, Brenda 120 Reputation points
2025-03-31T17:12:42.1433333+00:00

Re: How to change identity column in Clustered Index from int to big int for a 42gb table

I have a 43 gb table, with 30 mil rows, that has a clustered index with three fields. the third field is an integer identity column - col3 - that is now at 2147466707 and needs to have the data type altered to a big int. The clustered index is the only place where col3 is referenced. nothing is dependent on col3 except for two views build off the 42gb table. The table does have 13 other indexes but none of them reference col3. (I did not design this table)

How is the best way to do this? I have no idea how long it will take.

(1) Should I drop the clustered index, alter the column to bigint and then recreate the clustered index? Maybe it won't take too long since the rows are already in the clustered index order. (2) create a new table, with col3 as bigint, create clustered index, insert rows into new table from original table. then recreate the 13 other indexes. if this is the answer how can i do this without blowing up tempdb or any other system resources.

SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
177 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Naveen Kumar M 175 Reputation points
    2025-04-01T15:59:24.3+00:00

    Changing an identity column from INT to BIGINT in a table of this size is a significant operation that requires careful planning to minimize impact on performance and resources. Given your constraints, here are the best approaches and considerations:

    Approach 1: Drop and Recreate the Clustered Index

    1. Drop the Clustered Index – This will require exclusive access to the table and could be resource-intensive.
    2. Alter the Column to 'BIGINT' – Once the index is dropped, changing the column type should be relatively straightforward.
    3. Recreate the Clustered Index – Since the rows are already ordered, this step might not be as expensive as a full rebuild.

    However, be prepared for substantial log file usage.

    Pros: Simpler process, avoids full data movement.

    Cons: Dropping a clustered index will lock the table and could lead to downtime

    Approach 2: Create a New Table

    1. Create a new table with BIGINT for col3.
    2. Create the clustered index on the new table.
    3. Insert the data from the original table into the new one (INSERT INTO new_table SELECT * FROM original_table).
    4. Recreate the non-clustered indexes.
    5. Rename the tables (or swap them).

    Pros: Allows for minimal locking and can be done in stages.

    Cons: High system resource usage (especially for tempdb and transaction logs), requires space for a new table

    Estimated Time

    Exact timing depends on factors like disk speed, server load, and available resources. A full table copy (Approach 2) will take significantly longer than simply altering the column after dropping the index (Approach 1).

    Recommendation

    If downtime is acceptable, Approach 1 (drop index, alter column, recreate index) is simpler and cleaner. If availability and minimal locking are priorities, Approach 2 (creating a new table) offers more control at the cost of complexity.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.