How to add column with not null in sql server

November 13, at AM. Ben says:. August 25, at PM. Leave a Reply Cancel reply Enter your comment here Fill in your details below or click an icon to log in:.

Email required Address never made public. Name required. Follow me via Email Enter your email address to follow this blog and receive notifications of new posts by email. Join 3, other followers. Follow me on Twitter sqlstudent Blog at WordPress. Follow Following. SQL Studies Join 3, other followers. Sign me up. Already have a WordPress. Log in now. Loading Comments This gets worst with increase in data.

The easiest way to optimize this is to bulk insert the data into a new table along with the default values of the new columns and then adds necessary indexes, constraints, keys etc.

The above profiler snapshot shows that it took only 1. You will notice that I have even created two additional indexes just to get close to a real scenario where in a table might contain indexes and keys. The above profiler snapshot misses the update statements when compared with that of the previous SQL Server snapshot. Thus, it takes only milli seconds to add the columns, way fast than SQL Server If you don't allow NULL , and don't have a default , what is the value going to be?

If you also don't want a default value on your table, here's the full code for creating a column and dropping the default constraint pretty much instant even for large tables :.

Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 11 years, 5 months ago. Active 3 months ago. Viewed k times. Improve this question. Ryan Kohn Add a comment. Active Oldest Votes. Improve this answer. Pavel Morshenyuk Pavel Morshenyuk 10k 4 4 gold badges 31 31 silver badges 37 37 bronze badges.

I personally prefer the first way here if you have values you can put in the field manually. That way you don't have to worry about creating and deleting a default constraint where you don't need one. The dangerous update statement you mention would be detrimental in any query. It should be simple enough to see if you have an extra column in the update statement here. You would generally be adding only a column or two at a time.

If you happen to add an extra column into your update statement that doesn't belong there, in this example, then maybe you shouldn't be in charge of data in the first place.