Altering the type of a column in SQL Server (how to)

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.

Methods

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

BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_X
(
<< other columns here >>
,Y decimal(38, 4) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_DailyTotal SET (LOCK_ESCALATION = TABLE)
GO
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)’)
GO
DROP TABLE dbo.X
GO
EXECUTE sp_rename N’dbo.Tmp_X’, N’X’, ‘OBJECT’
GO
COMMIT

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

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in SQL Stuff and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s