How to alter column nvarchar length without drop

SqlSql ServerSsms

Sql Problem Overview

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).

Sql Solutions

Solution 1 - Sql

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

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

Solution 2 - Sql

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

Solution 3 - Sql

For constraints you can use following

DECLARE @DEFCONSTRAINT VARCHAR(1000);
DECLARE @DropDFConstraitCmd  VARCHAR(1000);
DECLARE @AlterDFColumnCmd VARCHAR(1000);
DECLARE @CreateDFConstraitCmd VARCHAR(1000);
DECLARE @CONSTRAINT_SAYI NUMERIC;
SET @DEFCONSTRAINT=(SELECT name
                      FROM sys.sysobjects
                     WHERE type = 'D' AND OBJECT_NAME(parent_obj) = 'TABLENAME' and name like 'DF__TABLENAME__COLUMNNAME%');
SET @CONSTRAINT_SAYI=(SELECT count(*)
                      FROM sys.sysobjects
                     WHERE type = 'D' AND OBJECT_NAME(parent_obj) = 'TABLENAME' and name like 'DF__TABLENAME__COLUMNNAME%');
SET @DropDFConstraitCmd   = ' ALTER TABLE TABLENAME DROP CONSTRAINT ' +@DEFCONSTRAINT;
SET @AlterDFColumnCmd     = ' ALTER TABLE TABLENAME ALTER COLUMN COLUMNNAME nvarchar(max) NOT NULL';
SET @CreateDFConstraitCmd = ' ALTER TABLE TABLENAME ADD CONSTRAINT ' +@DEFCONSTRAINT + ' DEFAULT ('' '') FOR COLUMNNAME ';
if @CONSTRAINT_SAYI > 0
    exec (@DropDFConstraitCmd);
exec (@AlterDFColumnCmd);
exec (@CreateDFConstraitCmd);

Solution 4 - Sql

try NTEXT instead.

ALTER TABLE Post ALTER COLUMN Body NTEXT NOT NULL;

NTEXT is similar to NVARCHAR, but is designed more for BODY fields or comment fields that require large amount of text.

Categories
Recommended Sql Solutions
Recommended Sql Server Solutions
Recommended Ssms Solutions

Previous Solution:

How to do a conditional count after groupby on a Pandas Dataframe?

PythonPandasDataframePandas Groupby

Next Solution:

Python Spark Cumulative Sum by Group Using DataFrame

Apache SparkPysparkSpark Dataframe

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionMário ToméView Question on Stackoverflow
Solution 1 - SqlUnhandledExcepSeanView Answer on Stackoverflow
Solution 2 - SqlChandra Sekhar kView Answer on Stackoverflow
Solution 3 - SqlAlper AkbulakView Answer on Stackoverflow
Solution 4 - SqlR2D2View Answer on Stackoverflow