none
Adding a column to a large (100 million rows) table with default constraint RRS feed

  • Question

  • IF NOT EXISTS (SELECT TOP 1 1  FROM dbo.syscolumns WHERE id = OBJECT_ID(N'dbo.Employee) and name = 'DoNotCall')
    BEGIN
    	ALTER TABLE [dbo].[Employee] ADD [DoNotCall] bit not null Constraint DoNot_Call_Default DEFAULT 0
    		
    	IF ( @@ERROR <> 0 )
    		GOTO QuitWithRollback
    END

    It just takes a LOT of time in SQL Server Management studio. I have to cancel the query and cancelling takes a whole lot time. I am using SQL Server 2008. Any advice anyone?
    • Edited by pp1299 Wednesday, April 24, 2013 4:25 PM
    Wednesday, April 24, 2013 4:23 PM

Answers

  • Yes, that is going to take a very long time.  It does an update to every one of those 100,000,000 million rows.  Furthermore, those changes are logged.  Which means that you also do many writes to the database log.  And when you kill it in the middle of these changes, SQL must rollback all of the changes to the rows it has processed, so the rollback will take approximately as long as the ALTER was running before you killed it.

    Possible choices

    1) Just do the alter as you have it and wait for it to complete.  If you do this, you had better have a large amount of space available for your database log, because it will get very large and if partway thru the update, you get an error because the log is full and cannot be expanded, the ALTER will error out and all of the changes will be backed out and you will have spent a long time and accomplished nothing.  This would probably be my least favorite choice

    2) Do an alter that adds the DoNotCall column without a default value and allows nulls.  That is ALTER TABLE [dbo].[Employee] ADD [DoNotCall] bit null.  That will run instantly.  Then write a loop that updates 10,000 or 100,000 rows at a time.  Then do ALTER TABLE [dbo].[Employee] ALTER COLUMN [DoNotCall] bit not null, then add the default constraint.  If you do this all in one transaction, you will still need lots of log space.  If you do it in multiple transactions, you don't need so much log space (as long as you do log backups during this process).  However, it will still take a long time.  Probably longer than Choice 1.

    3) Create a new table with the new column not null and the default value, copy the data from the old table to the new table, drop the old table, rename the new table as the old table.  Of course, you have to handle foreign key relationships.  This may or may not be faster than Choice 1.  And may or may not take as much space in the log as Choice 1 depending on whether or not the method you choose for the insert into the new table is minimally logged.

    4) Do an alter that adds the DoNotCall column without a default value and allows nulls.  That is ALTER TABLE [dbo].[Employee] ADD [DoNotCall] bit null.  Now have your application programs treat a NULL in DoNotCall or a 0 in DoNotCall as equivalent.  That can easily be done by having the check in the application always be IF DoNotCall = 1 ... Else ...  This method will be extremely fast and take no log space.

    5) Move to SQL 2012.  If you are on SQL 2012, you can do that ALTER command, ALTER TABLE [dbo].[Employee] ADD [DoNotCall] bit not null Constraint DoNot_Call_Default DEFAULT 0 and it will run extremely fast and use essentially no log space.

    Tom

    • Proposed as answer by Naomi NModerator Wednesday, April 24, 2013 5:32 PM
    • Marked as answer by pp1299 Wednesday, April 24, 2013 8:33 PM
    Wednesday, April 24, 2013 5:23 PM

All replies

  • Hi -

    When I have encountered a similar situation before, I ended up creating a copy/temp version of the table and then droped the original and renamed the new copy.  it has always performed better/faster for me when dealing with large volumnes of data (like you, 100+ million rows).

    Here is another link to an article on this topic too...seems to reinforce what i did and also mentions the transaction logging, which i didn't even think about.

    http://www.sqlservergeeks.com/blogs/AhmadOsama/personal/602/sql-server-2012-adding-not-null-columns-to-an-existing-table

    - will


    - will

    Wednesday, April 24, 2013 4:50 PM
  • Yes, that is going to take a very long time.  It does an update to every one of those 100,000,000 million rows.  Furthermore, those changes are logged.  Which means that you also do many writes to the database log.  And when you kill it in the middle of these changes, SQL must rollback all of the changes to the rows it has processed, so the rollback will take approximately as long as the ALTER was running before you killed it.

    Possible choices

    1) Just do the alter as you have it and wait for it to complete.  If you do this, you had better have a large amount of space available for your database log, because it will get very large and if partway thru the update, you get an error because the log is full and cannot be expanded, the ALTER will error out and all of the changes will be backed out and you will have spent a long time and accomplished nothing.  This would probably be my least favorite choice

    2) Do an alter that adds the DoNotCall column without a default value and allows nulls.  That is ALTER TABLE [dbo].[Employee] ADD [DoNotCall] bit null.  That will run instantly.  Then write a loop that updates 10,000 or 100,000 rows at a time.  Then do ALTER TABLE [dbo].[Employee] ALTER COLUMN [DoNotCall] bit not null, then add the default constraint.  If you do this all in one transaction, you will still need lots of log space.  If you do it in multiple transactions, you don't need so much log space (as long as you do log backups during this process).  However, it will still take a long time.  Probably longer than Choice 1.

    3) Create a new table with the new column not null and the default value, copy the data from the old table to the new table, drop the old table, rename the new table as the old table.  Of course, you have to handle foreign key relationships.  This may or may not be faster than Choice 1.  And may or may not take as much space in the log as Choice 1 depending on whether or not the method you choose for the insert into the new table is minimally logged.

    4) Do an alter that adds the DoNotCall column without a default value and allows nulls.  That is ALTER TABLE [dbo].[Employee] ADD [DoNotCall] bit null.  Now have your application programs treat a NULL in DoNotCall or a 0 in DoNotCall as equivalent.  That can easily be done by having the check in the application always be IF DoNotCall = 1 ... Else ...  This method will be extremely fast and take no log space.

    5) Move to SQL 2012.  If you are on SQL 2012, you can do that ALTER command, ALTER TABLE [dbo].[Employee] ADD [DoNotCall] bit not null Constraint DoNot_Call_Default DEFAULT 0 and it will run extremely fast and use essentially no log space.

    Tom

    • Proposed as answer by Naomi NModerator Wednesday, April 24, 2013 5:32 PM
    • Marked as answer by pp1299 Wednesday, April 24, 2013 8:33 PM
    Wednesday, April 24, 2013 5:23 PM
  • Thanks appreciate the responses. I really like the #5 above. Great to here this, but we are not ready yet for the switch. Thanks again.
    Wednesday, April 24, 2013 8:33 PM
  • Big thanks for this update.

    As I can't switch to SQL Server 2012, I've just tried method 2 and it worked much faster than method 1:

    on a table of 6 millions rows, method 1 was still running after 30 minutes, with no lock on the alter;

    method 2 just ended in 2 minutes.

    Greg

    Wednesday, May 6, 2015 8:53 AM