INF: ALTER TABLE with ADD COLUMN and DEFAULT May Take a Long Time on a Large Data Set (266694)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q266694

SUMMARY

An ALTER TABLE ADD COLUMN statement with a NOT NULL property and a specified default value might take a long time to complete. For example: 
ALTER TABLE Test ADD TestColumn INT NOT NULL DEFAULT -1

MORE INFORMATION

If a column is nullable, there is no need to update every row in the table. However, if you specify NOT NULL the DEFAULT value is entered in every row of the table by SQL Server. This has the same performance impact as if you were to issue an UPDATE statement for every row in the table. As with any UPDATE statement, the magnitude of the performance impact varies depending on many factors.

Factors that may affect the speed of this update include:
  • The number of rows in the table.

  • The FILLFACTOR.

  • The number of page splits if any splits are necessary.

  • The need for the data and/or log to grow (and in what increments).

  • The size of the column being added.

  • The number of indexes, especially if there is a large number of page splits.

  • The hardware specifications: Clock speed and number of CPUs, disk RPMs and number of disks (RAID), amount of RAM.
Modification Type:MajorLast Reviewed:10/31/2003
Keywords:kbinfo KB266694
©2003 Microsoft Corporation. All rights reserved.