Q:

How to Alter Column from nvarchar(max) to nvarchar(50)

I have an existing table in SQL SERVER 2008 with one of its column as NVARCHAR(MAX) and it only has values of less than 10 characters in it.
This table is in production and has data in it.
I have got a requirement wherein I have to Alter this column from NVARCHAR(MAX) to NVARCHAR(50). The SQL Server gives some Truncation error while doing this operation, even though the data in that column is less than 10 characters.
This is my script:
ALTER TABLE [dbo].[Table] ALTER COLUMN [Column1] NVARCHAR ( 50 ) NOT NULL 

A:

First Check Your table data with this query:
SELECT DATALENGTH(Column_Name) AS FIELDSIZE, Column_Name 
FROM Table_Name 

If everything is fine, you may have checked the Prevent Saving Changes option.   Follow these steps to check:
Tools > Designers Uncheck Prevent saving changes that require table re-creation 

