alter column

Question

alter column in large table takes lot of time and sometimes does not complete(needs restart of server). Please help me by informing which data type conversions execute instantly and which conversions take time.

Answer 1

There are three levels of ALTER TABLE ALTER COLUMN:

1. Pure metadata change.
2. Metadata change, but needs to be validated.
3. Table has to be rebuilt entirely.

Pure metadata changes are when you are making a varchar, nvarchar or varbinary column longer.

But if you change to a datatype with a different format or size, the table has to be rebuild. For instance int to bigint requires the table to be rebuilt. Same goes for changing from char(4) to char(6).

The middle ground, when validation is required is when you change a column from permitting NULL to be NOT NULL. Validation is also required when you make a varchar, nvarchar or varbinary column shorter. The validation is required to make sure that there no values that violate the new definition.