Altering the type of a column in SQL Server
I regularly come across this problem due to changing specifications. If you create a database for a development team then you will come across this issue. Here’s the sequence.
• Creates a table with an integer column and a default value and marks it as not null.
• Insert a lot of data in to the table.
• Change is required to the type of the column to something bigger like a decimal
I recently created a table to hold weekly sales data for many years which amounted to 162 million rows. There is a column in the table that is going to be used as part of a calculation. It was created as part of the table at build time but is not yet populated with anything meaningful. We have found that we cannot fill this column due to an arithmetic overflow and have decided to expand the type from integer to decimal.
Note the following methods for changing a column and their implications…
1. Open the table in management studio and alter the column to be decimal
2. Issue an ALTER TABLE ALTER COLUMN command to change the type of the column
3. Drop the column and then add a new one using the ALTER TABLE ADD COLUMN command.
Note the differences in the performance
Remember that although the column’s existing value is not important, the rest of the table is and it is very big.
Method 1 – Management studio script
CREATE TABLE dbo.Tmp_X
<< other columns here >>
,Y decimal(38, 4) NOT NULL
) ON [PRIMARY]
ALTER TABLE dbo.Tmp_DailyTotal SET (LOCK_ESCALATION = TABLE)
IF EXISTS(SELECT * FROM dbo.X)
EXEC(‘INSERT INTO dbo.Tmp_X (a,b,c,d,e,f,y)
SELECT a,b,c,d,e,f, CONVERT(decimal(38, 4), Y) FROM dbo.X WITH (HOLDLOCK TABLOCKX)’)
DROP TABLE dbo.X
EXECUTE sp_rename N’dbo.Tmp_X’, N’X’, ‘OBJECT’
This is the slowest method and probably won’t succeed at all – Management studio creates a transaction within which it creates a temp copy of the table, inserts all the data to it, drops the original table and renames the copy, closing the transaction at the end. Table is locked for the full duration of the transaction which in a table of many millions can take forever (literally if the table is in use for updates).
Method 2 – Use ALTER COLUMN command
ALTER TABLE dbo.X ALTER COLUMN Y decimal(38, 4) NOT NULL
Faster – Can lock out though. SQL will change the type but to do so will have to first check that every row in the table will fit with the new column, presenting an error if data will be lost. If you need the data then this is a good option.
Fastest – If you do not need the data that exists in the column then this is the best method.
Method 3 – Drop and re-create the column
ALTER TABLE dbo.DailyTotal DROP COLUMN WeekVolume
ALTER TABLE dbo.DailyTotal ADD WeekVolume decimal(38, 4) NULL