Skip to main content
replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
Source Link

The script that SSMS generates is not the best one.

You should be able to use simple ALTER TABLE:

ALTER TABLE TableName
ALTER COLUMN ColumnName VARCHAR(255) NOT NULL

Obviously, make sure in advance that the text that you store in this column would fit into 255 characters limit.

I'd strongly recommend to try it in test environment first to get an idea how long it would take. I'm not sure, and it may depend on the version and edition of your server, but it is likely that this kind of change would be performed not as a metadata change, which means that for a terabyte of data it would take a significant amount of time.


This question (Why does ALTER COLUMN to NOT NULL cause massive log file growth?Why does ALTER COLUMN to NOT NULL cause massive log file growth?) has a lot of details about ALTER COLUMN.

The script that SSMS generates is not the best one.

You should be able to use simple ALTER TABLE:

ALTER TABLE TableName
ALTER COLUMN ColumnName VARCHAR(255) NOT NULL

Obviously, make sure in advance that the text that you store in this column would fit into 255 characters limit.

I'd strongly recommend to try it in test environment first to get an idea how long it would take. I'm not sure, and it may depend on the version and edition of your server, but it is likely that this kind of change would be performed not as a metadata change, which means that for a terabyte of data it would take a significant amount of time.


This question (Why does ALTER COLUMN to NOT NULL cause massive log file growth?) has a lot of details about ALTER COLUMN.

The script that SSMS generates is not the best one.

You should be able to use simple ALTER TABLE:

ALTER TABLE TableName
ALTER COLUMN ColumnName VARCHAR(255) NOT NULL

Obviously, make sure in advance that the text that you store in this column would fit into 255 characters limit.

I'd strongly recommend to try it in test environment first to get an idea how long it would take. I'm not sure, and it may depend on the version and edition of your server, but it is likely that this kind of change would be performed not as a metadata change, which means that for a terabyte of data it would take a significant amount of time.


This question (Why does ALTER COLUMN to NOT NULL cause massive log file growth?) has a lot of details about ALTER COLUMN.

Source Link
Vladimir Baranov
  • 4.7k
  • 4
  • 26
  • 42

The script that SSMS generates is not the best one.

You should be able to use simple ALTER TABLE:

ALTER TABLE TableName
ALTER COLUMN ColumnName VARCHAR(255) NOT NULL

Obviously, make sure in advance that the text that you store in this column would fit into 255 characters limit.

I'd strongly recommend to try it in test environment first to get an idea how long it would take. I'm not sure, and it may depend on the version and edition of your server, but it is likely that this kind of change would be performed not as a metadata change, which means that for a terabyte of data it would take a significant amount of time.


This question (Why does ALTER COLUMN to NOT NULL cause massive log file growth?) has a lot of details about ALTER COLUMN.