3

I have a table that has more than 1 billion rows. I have a requirement to add a new column to this table.

Alter Table mylargetable
Add newColumn varchar(50) NULL

I'm concerned that this might take a very long time to run. I know that if we pass default values, it can take a very long time but it is the same behavior with creating the column with a null value? Is there a better way to do this?

lifeisajourney
  • 751
  • 1
  • 8
  • 20

1 Answers1

1

Adding a nullable column should be fairly quick. SQL Server does not actually allocate that space on the disk to existing records until there is an actual value that it needs to store. Keep in mind that when you start assigning values, particularly to a large number of rows, things will take longer. The new record size will take up more space, and if there isn't room it will need to relocate the record elsewhere.

If you need to eventually make this NOt NULL, you will want to start by adding a default (to mitigate new NULL values) and then methodically go through the records assigning a value in chunks. Only once all records actually have the value should you alter the column. This will take a little while (it needs to check that all records have a value, and will set the value using the default if needed), but much faster than having to reorganize your whole table.

Graham
  • 619
  • 4
  • 12