How to change the data type of a column of a large table

Here is another common question appeared in forums, how to change the data type of column, specifically a large table. If you search for it, the most common one is, handling this with a new column (which is my 3rd way in the code). However, situation to situation, things can be different, hence tested few ways of changing the type with a table that contains 7 millions records.

Here are 3 ways I tried with. The code uses 3 tables called dbo.Sales_1, dbo.Sales_2, and dbo.Sales_3. The structure and the number of records are same for all 3 tables. Here is the test I did.

view plainprint?

1. -- Method 1  
2. -- ========  
3.   
4. -- Check the fragmentation of the table  
5. SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.Sales_1'), null, null, 'detailed');  
6. -- Result  
7. -- External Fragmentation - 0.3% (Good)  
8. -- Internal Fragmentation - 98% (Good)  
9.   
10. -- Changing the column type of Product key from int to bigint  
11. ALTER TABLE dbo.Sales_1  
12.  ALTER COLUMN ProductKey bigint not null;  
13. -- Result  
14. -- Time spent: 4:47  
15.   
16. -- Checking fragmentation again  
17. SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.Sales_1'), null, null, 'detailed');  
18. -- Result  
19. -- External Fragmentation - 99% (Bad)  
20. -- Internal Fragmentation - 51% (Bad)  
21.   
22. -- Rebulding the index  
23. ALTER INDEX PK__Sales_1 ON dbo.Sales_1 REBUILD;  
24. -- Result  
25. -- Time spent: 3:43  
26.   
27. -- **  
28. -- Total time spent: 8:30  
29. -- **  
30.   
31.   
32.   
33. -- Method 2  
34. -- ========  
35.   
36. -- Check the fragmentation of the table  
37. SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.Sales_2'), null, null, 'detailed');  
38. -- Result  
39. -- External Fragmentation - 0.3% (Good)  
40. -- Internal Fragmentation - 98% (Good)  
41.   
42. -- Rebuilding the index with filfactor 80.  
43. ALTER INDEX PK__Sales_2 ON dbo.Sales_2 REBUILD WITH (FILLFACTOR = 80);  
44. GO  
45. -- Result  
46. -- Time spent: 1:38  
47.   
48. -- Changing the column type of Product key from int to bigint  
49. ALTER TABLE dbo.Sales_2  
50.  ALTER COLUMN ProductKey bigint not null  
51. -- Result  
52. -- Time spent: 1:13  
53.   
54. -- Check for fragmentation  
55. SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.Sales_2'), null, null, 'detailed');  
56. -- Result  
57. -- External Fragmentation - 0.1% (Good)  
58. -- Internal Fragmentation - 85% (Good)  
59.   
60. -- **  
61. -- Total time spent: 2:51  
62. -- **  
63.   
64.   
65.   
66. -- Method 3  
67. -- ========  
68.   
69. -- Check the fragmentation of the table  
70. SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.Sales_3'), null, null, 'detailed');  
71. -- Result  
72. -- External Fragmentation - 0.3% (Good)  
73. -- Internal Fragmentation - 98% (Good)  
74.   
75. -- Add a new column with required data type: bigint  
76. -- Setting it as a nullable column  
77.  ALTER TABLE dbo.Sales_3  
78.  ADD NewProductKey bigint null  
79.   
80. -- Changing the recovery model  
81. ALTER DATABASE TestDatabase SET RECOVERY Bulk_Logged;  
82.   
83. -- Updating the new column with old column value  
84. DECLARE @Count int = 1  
85.   
86. WHILE (@Count != 0)  
87. BEGIN  
88.   
89.  UPDATE TOP (500000) dbo.Sales_3 WITH (TABLOCK)  
90.   SET NewProductKey = ProductKey  
91.  WHERE NewProductKey IS NULL  
92.   
93.  SET @Count = @@ROWCOUNT  
94. END  
95. -- Result  
96. -- Time spent: 1:23  
97.   
98. -- Drop the old column  
99. ALTER TABLE dbo.Sales_3  
100.  DROP COLUMN ProductKey  
101.   
102. -- Rename the new column as old column  
103. sp_rename 'dbo.Sales_3.NewProductKey', 'ProductKey', 'COLUMN';  
104.   
105. -- Alter the current column as non-nullable  
106. ALTER TABLE dbo.Sales_3  
107.  ALTER COLUMN ProductKey bigint NOT NULL  
108. -- Result  
109. -- Time spent: 0.03  
110.   
111. -- Check the fragmentation of the table  
112. SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.Sales_3'), null, null, 'detailed');  
113. -- Result  
114. -- External Fragmentation - 99% (Bad)  
115. -- Internal Fragmentation - 51% (Bad)  
116.   
117.   
118. ALTER INDEX PK__Sales_3 ON dbo.Sales_4 REBUILD;  
119. -- Result  
120. -- Time spent: 2:55  
121.   
122. -- Change back the recovery model  
123. ALTER DATABASE TestDatabase SET RECOVERY Full;  
124.   
125. -- **  
126. -- Total time spent: 4:21  
127. -- **  

-- Method 1
-- ========

-- Check the fragmentation of the table
SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.Sales_1'), null, null, 'detailed');
-- Result
-- External Fragmentation - 0.3% (Good)
-- Internal Fragmentation - 98% (Good)

-- Changing the column type of Product key from int to bigint
ALTER TABLE dbo.Sales_1
 ALTER COLUMN ProductKey bigint not null;
-- Result
-- Time spent: 4:47

-- Checking fragmentation again
SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.Sales_1'), null, null, 'detailed');
-- Result
-- External Fragmentation - 99% (Bad)
-- Internal Fragmentation - 51% (Bad)

-- Rebulding the index
ALTER INDEX PK__Sales_1 ON dbo.Sales_1 REBUILD;
-- Result
-- Time spent: 3:43

-- **
-- Total time spent: 8:30
-- **



-- Method 2
-- ========

-- Check the fragmentation of the table
SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.Sales_2'), null, null, 'detailed');
-- Result
-- External Fragmentation - 0.3% (Good)
-- Internal Fragmentation - 98% (Good)

-- Rebuilding the index with filfactor 80.
ALTER INDEX PK__Sales_2 ON dbo.Sales_2 REBUILD WITH (FILLFACTOR = 80);
GO
-- Result
-- Time spent: 1:38

-- Changing the column type of Product key from int to bigint
ALTER TABLE dbo.Sales_2
 ALTER COLUMN ProductKey bigint not null
-- Result
-- Time spent: 1:13

-- Check for fragmentation
SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.Sales_2'), null, null, 'detailed');
-- Result
-- External Fragmentation - 0.1% (Good)
-- Internal Fragmentation - 85% (Good)

-- **
-- Total time spent: 2:51
-- **



-- Method 3
-- ========

-- Check the fragmentation of the table
SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.Sales_3'), null, null, 'detailed');
-- Result
-- External Fragmentation - 0.3% (Good)
-- Internal Fragmentation - 98% (Good)

-- Add a new column with required data type: bigint
-- Setting it as a nullable column
 ALTER TABLE dbo.Sales_3
 ADD NewProductKey bigint null

-- Changing the recovery model
ALTER DATABASE TestDatabase SET RECOVERY Bulk_Logged;

-- Updating the new column with old column value
DECLARE @Count int = 1

WHILE (@Count != 0)
BEGIN

 UPDATE TOP (500000) dbo.Sales_3 WITH (TABLOCK)
  SET NewProductKey = ProductKey
 WHERE NewProductKey IS NULL

 SET @Count = @@ROWCOUNT
END
-- Result
-- Time spent: 1:23

-- Drop the old column
ALTER TABLE dbo.Sales_3
 DROP COLUMN ProductKey

-- Rename the new column as old column
sp_rename 'dbo.Sales_3.NewProductKey', 'ProductKey', 'COLUMN';

-- Alter the current column as non-nullable
ALTER TABLE dbo.Sales_3
 ALTER COLUMN ProductKey bigint NOT NULL
-- Result
-- Time spent: 0.03

-- Check the fragmentation of the table
SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.Sales_3'), null, null, 'detailed');
-- Result
-- External Fragmentation - 99% (Bad)
-- Internal Fragmentation - 51% (Bad)


ALTER INDEX PK__Sales_3 ON dbo.Sales_4 REBUILD;
-- Result
-- Time spent: 2:55

-- Change back the recovery model
ALTER DATABASE TestDatabase SET RECOVERY Full;

-- **
-- Total time spent: 4:21
-- **

Here is the summary of it;

Method	Time Spent	Comments
1 - Just alter the column	8:30	Takes long time
2 - Alter the column with fill-factor	2:51	Fastest, Probably the best
3 - With a new column	4:21	Good, but need more extra work

My preference is for the highlighted one as it gives the best performance and I do not see any issue with it.