Search code examples
sql-servernullsql-updatealter

Is NULL necessary?

Is NULL or NOT NULL necessary for Adding a column in SQL Server? (If you are going to run UPDATE statement after)

I tested without it locally, and it seems to work fine both in SQL and on the website; but I wanted to make sure before running release/production.

I looked up some other articles, including microsoft website. Some show it with, some without. SO articles say some benefits of NULL, like if you have information that may be added later. But assuming I am going to run UPDATE to add values after, will it matter?

I am guessing it does not matter from what I've tested and read.

ALTER TABLE [dbo].[MyTable] ADD NewColumn varchar(150);
UPDATE [dbo].[MyTable] SET NewColumn ='Math' WHERE ID = 1
UPDATE [dbo].[MyTable] SET NewColumn ='Science' WHERE ID = 2
Solution

  • You can skip it, and by default the column will be created as NULL. However it is more legible if you indicate it explicitly.

    Keep in mind that if your table already has data, you CANNOT add the column as NOT NULL. For this, you should firstly add the column as NULL, then UPDATE the values with non-null valid data and then alter column to NOT NULL.

    Edit: Assuming the default behavior of the sql server when adding columns.