SQL Server Performance for Alter Table Alter Column Change Data Type

SQL Server performance for alter table alter column change data type

Coincidentally, I had to do something very similar about 3 hours ago. The table was 35m rows, it is fairly wide, and it was taking forever to just do this:

alter table myTable add myNewColumn int not null default 0;

Here's what what I ended up going with:

alter table myTable add myNewColumn int null;

while 1=1
begin
    update top (100000) myTable
    set
        myNewColumn = 0
    where
        myNewColumn is null;

    if @@ROWCOUNT = 0 break;
end

alter table myTable alter column myNewColumn int not null;
alter table myTable add constraint tw_def_myNewColumn default (0) for myNewColumn;

This time around, the alter table statements were near-instant. It took about 7-8 minutes (on a slow server) to do the update batches. I'm speculating that SQL Server was generating undo in my original query to restore the values, but I didn't expect that starting off.

Anyway, in your case, maybe something similar would help. You could try adding a new bigint column, update the new column in batches, then set the constraints on it.

Change column types in a huge table

Depending on what change you are making, sometimes it can be easier to take a maintenance window. During that window (where nobody should be able to change the data in the table) you can:

1. drop any indexes/constraints pointing to the old column, and disable triggers
2. add a new nullable column with the new data type (even if it is meant to be NOT NULL)
3. update the new column setting it equal to the old column's value (and you can do this in chunks of individual transactions (say, affecting 10000 rows at a time using UPDATE TOP (10000) ... SET newcol = oldcol WHERE newcol IS NULL) and with CHECKPOINT to avoid overrunning your log)
4. once the updates are all done, drop the old column
5. rename the new column (and add a NOT NULL constraint if appropriate)
6. rebuild indexes and update statistics

The key here is that it allows you to perform the update incrementally in step 3, which you can't do in a single ALTER TABLE command.

This assumes the column is not playing a major role in data integrity - if it is involved in a bunch of foreign key relationships, there are more steps.

EDIT

Also, and just wondering out loud, I haven't done any testing for this (but adding it to the list). I wonder if page + row compression would help here? If you change an INT to a BIGINT, with compression in place SQL Server should still treat all values as if they still fit in an INT. Again, I haven't tested if this would make an alter faster or slower, or how much longer it would take to add compression in the first place. Just throwing it out there.

How do you change the datatype of a column in SQL Server?

ALTER TABLE TableName 
ALTER COLUMN ColumnName NVARCHAR(200) [NULL | NOT NULL]

EDIT
As noted NULL/NOT NULL should have been specified, see Rob's answer as well.

Changing a datatype in a table without reaching the transaction log limits

You might find it faster to create a new table, truncate the old, and then re-insert:

select t.*
into temp_my_table
from my_table t;

truncate table my_table;  -- back it up first!

alter table mytable alter column comment nvarchar(max);  -- no need to be cheap here

insert into mytable
    select t.*
    from t;

One caveat: You may have to pay attention if you have insert triggers or identity columns.

How to change data type of a column in an SQL table from integer to decimal

Easy - just run this SQL statement

ALTER TABLE dbo.Budget
ALTER COLUMN ROE DECIMAL(20,2)   -- or whatever precision and scale you need.....

See the freely available MSDN documentation on what exactly the precision, scale and length in decimal numbers are and what ranges of values are possible

Preserve SQL Indexes While Altering Column Datatype

You can not change the datatype from smalldatetime to datetime with the indexes, unique constraints, foreign key constraints or check constraints in place. You will have to drop them all prior to changing the type. Then:

alter table T alter column TestDate datetime not null

Then recreate the constraints and indexes that still apply.

---

Some different approaches for generating the drop and creates:

1) If you have given explicit names to all indexes and constraints then your installer can run a static script in each environment (dev, test, user acceptance testing, performance testing, etc, production.)

To generate this explicit script you can:
a) Use SSMS (or with SQL Server 2000, enterprise manager) to script the create and drop statements.
b) Work from you source code repository to discover the names and definitions of the dependent objects and put together the appropriate static script.
c) Attempt to run the alter statement. See what it fails on. Look up the definitions and hand write the drop and create. (Personally, this would be great for writing the drop, not so good at the create.)

2) If you have not given explicit names to all indexes and constraints, then your installer will have to query the data dictionary for the appropriate names and use dynamic SQL to run the drops, in the correct order, prior to the alter column statement and then the creates, in the correct order, after the alter column.

This will be simpler if you know that there are no constraints, and just indexes.

There may be tools or libraries that already know how to do this.

Also, if this is a packaged application, you may not be assured that the local DBAs have not added indexes.

NOTE: If there is a unique constraint, it will have built an index, which you will not be able to drop with DROP INDEX.

SQL change column data type with lots of data

All millions of rows have to changed at the same time in one transaction

Another option is to create a new table, insert in batches, drop old table, rename new table.
However, it's liable to take longer this way.

---

Related Topics