I have a table in SQL Server 2008 R2 with close to a billion rows with Composit PK Column. I want to change the Datatype of Columns from Date to Datetime. Two times ALTER TABLE zzz ALTER COLUMN yyy works, but it's very slow. How can I speed the process up? I was thinking to copy the data to another table, drop, create, copy back and switching to simple recovery mode or somehow doing it with a cursor a 1000 rows a time but I'm not sure if those will actually lead to any improvement.
Sql-server – Change Data Types large Live table
sql server
Related Question
- Sql-server – Disk Space Considerations for Rebuilding a large Clustered Primary Key Index
- Sql-server – Timestamp-based index
- Sql-server – What’s the best way to archive all but current year and partition the table at the same time
- Sql-server – Alter All Data Types To Different Data Type
- Sql-server – Will altering an nvarchar(max) column to nvarchar(50) lock the table
- Sql-server – Altering Column Size On a Super Large Table
Best Answer
Summary
For large tables, it is nearly always preferable to move the data instead of altering the table. To get maximum speed, use one of the following patterns. Let us call the table you want to change
S
(for source).S
. Let us call thatT
ALTER TABLE
onT
. This is very fast becauseT
has no rowsT
has a cluster index, remove all other indexes but the clusterT
is a heap, remove all indexesT
S
and rename toT
Copy Data
In step 6 above, you will need to move data from
S
toT
at highest possible speed. The following are two options:INSERT ... SELECT
Of these two, SSIS is the fastest. With
INSERT ... SELECT
you are restricted to a single thread. However,INSERT ... SELECT
is much easier. See below for more details.Option 1: INSERT ... SELECT
If
T
is a heap, do this:This is minimally logged.
If
T
has a clustered index, do this:This is also be minimally logged. The problem with this option is that you can only run one
INSERT
statement in parallel. Because SQL Server does not do parallelINSERT ... SELECT
, this restricts you to around 40-80MB/sec - which is quite slow.Option 2: Use SSIS
This option is a lot more trouble, but also faster. The idea is this:
T
S
(using some filter that is supported by an index). The SSIS package then moves the rows toT
using theTABLOCK
hint in bulk modeS
(so you don't duplicate rows inT
)This is MUCH faster because the packages can run in parallel. With this technique, I can typically move tables at GB/sec.