Home > DeveloperSection > Forums > ALTER TABLE with a default: SQL Server vs Oracle
Goti Bandu

Total Post:119

Points:835
Posted on    May-04-2013 2:07 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 760  View(s)
Rate this:
Hi Mindstickians!

We have some tables in an Oracle database with several million rows. When we alter one of these tables to add a new column, we specify a default. This is very slow to 

run as Oracle has to update all existing rows with the default. The solution is to ensure the column is defined as NOT NULL because then Oracle (recent versions only) 

will not update all existing rows with the default - the subsequent presence of a null in one of these columns tells Oracle that it requires a default and it will 

provide the default on the fly.

My question is regarding SQL Server: does it exhibit similar behaviour when adding a column and providing a default? If not, are there any best practices in 

efficiently adding new columns with default values, and are there any advantages in defining a column as NOT NULL?

Thanks in advance!


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    May-04-2013 9:04 AM

Hi Goti!

Prior to SQL Server 2012 adding a NULLable column w/o default was very vast, but adding a DEFAULT contraint would be slow, as every row has to be updated. Since SQL Server 2012 http://rusanu.com/2011/07/13/online-non-null-with-values-column-add-in-sql-server-11/ when possible.


Don't want to miss updates? Please click the below button!

Follow MindStick