Microsoft KB Archive/266694

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

Article ID: 266694

Article Last Modified on 10/31/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



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.

Additional query words: modify change field

Keywords: kbinfo KB266694

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.