How to shorten column length of nvarcha type in Transact-SQL?

Question

When i use alter table alter column,i can lengthen the length of a column of nvarchar type.

But when i use the same way to shorten column length,it issue an error.

I know how to do this in Enterprise Manager,but not in Transact-SQL.

Answer 1

If you make the change in Enterprise Manager but, innstead of saving your changes, click on the "Save Change Script" button on the toolbar and it will give you the T-SQL you need to make this change.

In this instance you will see that an ALTER COLUMN is not issued. Behind the scenes the table is actually dropped and recreated.

-Jamie

Answer 2

You can use ALTER TABLE...ALTER COLUMN to reduce the length of the column as long as the data in your column will fit. See Books Online for more details. In general, you do not want to rely on the UI to make schema changes since it doesn't do the operation in the most efficient way and lot of cases it drops & recreates the table which is resource intensive.

Answer 3

Thank