How to alter column nvarchar length without drop

Question

I'm trying to alter the length of the column "Body" in table "Post" like this:

ALTER TABLE Post ALTER COLUMN Body nvarchar(8000) NOT NULL;

The column is defined as nvarchar(4000) and it gives me this error:

Msg 2717, Level 16, State 2, Line 1 The size (8000) given to the parameter 'Body' exceeds the maximum allowed (4000).

Answer 1

Use Max instead. If the column were declared as VARCHAR, 8000 would be ok.

ALTER TABLE Post ALTER COLUMN Body nvarchar(max) NOT NULL;

Answer 2

In case, if the column has any constraint key like default, check .. ext, First we have dropped the key constraint from the column than alter the size of the column and alter your constraint to the column. The below steps will help you.

Steps are,

>  ALTER TABLE MESSAGE_INBOX_VIEWERS DROP CONSTRAINT DF_CONST_TEXT
>  ALTER TABLE MESSAGE_INBOX_VIEWERS ALTER COLUMN TEXT NVARCHAR(MAX) 
>  ALTER TABLE MESSAGE_INBOX_VIEWERS ADD CONSTRAINT DF_CONST_TEXT DEFAULT('') FOR TEXT