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
  • 505
  • 5
  • 17
  • We're on SQL Server 2012. I did not run it on a test system since I do not have a table that has a large amount of data. I'm not really familiar with checking the logs and blocking. Is there any article I can refer to for that? –  Mar 22 '22 at 18:42
  • 1
    I'd still recommend testing it, but I think this should be an online operation and very quick. SQL Server 2012+ can add NOT NULL columns with constant as the default as an online operation, so I'd imagine a NULL column would also be online, probably just needs a quick schema lock. See section Adding NOT NULL Columns as an Online Operation https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15#adding-not-null-columns-as-an-online-operation – Stephan Mar 22 '22 at 19:54
  • @Stephan note that it is an online operation only in Enterprise (and Developer) Editions. – ypercubeᵀᴹ Nov 24 '22 at 17:52

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
  • 609
  • 4
  • 12