Alter not null in sql server

The above profiler snapshot shows that the SQL Server runs an update statement to set the column values as specified by the default constraint. An update is a fully logged statement and thus takes 19 seconds to update rows. 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. It depends on your back fill strategy or forward fill strategy. Decide if the column values need to be unique or non-unique. Otherwise, see if performance is adequate or if you need to add an index. 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. Altering a column: null to not null Ask Question. Asked 12 years, 9 months ago. Active 8 months ago. Viewed 1. Using SQL server Marko Karmic Coder Karmic Coder 17k 5 5 gold badges 31 31 silver badges 41 41 bronze badges.

Add a comment. Active Oldest Votes. Arsen Khachaturyan 7, 4 4 gold badges 36 36 silver badges 38 38 bronze badges. Let me value-add to the above two correct responses. The semantics of NULL can have a few meanings. Before you did the above recommendations. Make sure you application does not take null in its business logic. Backup your database first in case you make a typo and your DB explodes. Databases should always be backed up.

SebastianGodelet: there's a setting that allows you to turn off that warning, or make it so that it doesn't prevent you from modifying the table. In some cases, changing the schema of a table requires that a new table be created, the data copied over from the old and the old table dropped. Because an error in this process could result in data loss, SSMS warns you and, by default, prevents you from doing it.

I could automatically generate the code by using a schema-dumper program which outputs the existing datatype of each column, but that too has risks, if the dumper program is not aware of the latest datatypes and outputs something else as an example, suppose it doesn't know about datetime2 and writes out datetime instead.

The SQL server already knows what the column type is, so surely there is a way to tell it to keep that and just turn off the nullability. If there's really no way to do it except by finding the existing datatype to put it into the DDL, perhaps you could recommend a suitable tool to use? I know about dbschema. If you have multiple schemas, you will need to accommodate that too. Use your text parsing tools of choice to extract the column definitions from the result.

The 'two approaches' answer suggested by Anon is helpful. The website's comment box doesn't allow enough text so I will post my final answer here. This query tries to reverse-engineer the type for each column:. Then you can take each row from this query and do a check that no nulls exist before running 'alter table'. I am doing something like the following:. If the second query returns 1 then you can probably make the 'alter table' change.

Or, if you feel brave you could just run all the 'alter table' statements and let them fail if the column does contain nulls. Oddly, I don't have permissions on the database to right-click in Management Studio and 'script database as', although I can do it for individual objects. 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 8 years, 1 month ago. Active 4 years, 7 months ago. Viewed 2k times.