CODEWITHSUNDEEP
sql-serverdatabasedatabase-schemaddlalter-column
George Xu
George Xu

Reputation: 79

Behind the scene operations for ALTER COLUMN statement in SQL Server

I am altering the column datatype for a table with around 100 Million records using the below query:

ALTER TABLE dbo.TARGETTABLE
ALTER COLUMN XXX_DATE DATE

The column values are in the right date format as I inserted original date from a valid data source.

However, the query have been running for a long time and even when I attempt to cancel the query it seems to take forever.

Can anyone explain what is happening behind the scene in SQL Server when an ALTER TABLE STATEMENT is executed and why requires such resources?

Upvotes: 2

Views: 561

Answers (2)

gotqn
gotqn

Reputation: 43636

Another approach to do this is the following:

  1. Add new column to the table - [_date] date
  2. Using batch update you can change transfer the values from the old to the new column without blocking the table for the other users.

Then in one transaction do the following:

  1. update all of the new values inserted after the update is done
  2. drop the old column
  3. rename the new column

Note, if you have an index on this field you need to drop it before deleting the old column and create if after renaming the new one.

Upvotes: 2

THE LIFE-TIME LEARNER
THE LIFE-TIME LEARNER

Reputation: 1522

There are a lot of variables that will make these Alter statements make multiple passes through your table and make heavy use of TempDB and depending on efficiency of TempDB it could be very slow.
Examples include whether or not the column you are changing is in the index (especally clustered index since non-clustering key carries the clustering index).

Instead of altering table...i will give you one simple exmaple...so you can try this....

  1. Suppose your table name is tblTarget1
  2. Create the another table (tblTarget2) with same structure...
  3. Change the dataType of tblTarget2.....
  4. Copy the data from tblTarget1 To tblTarget2 using Insert into query....
  5. Drop the original table(tblTarget1)
  6. Rename the tblTarget2 as tblTarget1

The main Reaseon is that....changing the data type will take a lot of data transfer and data page alignment....

For more Information you can follow this Link

Upvotes: 2

Related Questions