Alter Table: Add Not Null Column – SQL Server

Listen up my people.  NOT NULL columns are your friend.  It may take a few extra brain cells to populate it initially but the long term rewards in data quality are well worth it. (You'll also force all your software developer cohorts to think specifically about the values they'll be inserting into a column which is always a good thing).

But wait!  I have an existing table I want to add a column to.  How can I add a NOT NULL column to an existing table without dropping and reloading the table?  Have no fear true believer.  SQL Server can accommodate this scenario with ease.  Simply, craft a little T-SQL which will add the column and the default constraint at the same time.  It's as easy as pie.  (Somehow I got in the habit of some sort of pop culture tie in in most of these posts.  I don't really have something concrete this time but since I did say "pie": Have you seen Waitress?  It's great movie with a tragic real life story involving the actress/writer/director Adrienne Shelly).

Need to add a NOT NULL column to an existing table which already has rows?  Here you go:


This code will add the new NOT NULL column NewColumnA to the table TableA.  It will also add a default constraint to the column that will place the value "n/a" in the column for all the existing rows.

Not Null Pie - Try it and love it.

By Phil Steffek

Phil Steffek is a professional sumo wrestler who is hoping to represent the U.S. in the next Olympics. During the off season Phil is a Data Architect working on transactional databases, data warehouse implementations, and all aspects of business intelligence. Also, Phil is not really a sumo wrestler. You can get to know a bit more about my non-sumo doings by checking out my LinkedIn Page

Leave a comment

Your email address will not be published.