Altering column size to more than 4000 on nvarchar...

when i run
ALTER TABLE States ALTER COLUMN StateComment  nvarchar(5000) NULL

i get the below error.. this is sql 2008.. so i thought we should have bigger option?

The size (5000) given to the parameter 'StateComment' exceeds the maximum allowed (4000).
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

25112Author Commented:
i dont want to do a nvarchar(max).. but nvarchar(5000) would suffice. nvarchar(4000)  wont be enough.
As far as I know if you need to insert more than 4000 you have to use nvarchar(max), there is no way to set nvarchar to 5000.
Lee SavidgeCommented:
nvarchar(max) is your only option

Why don't you want to use one?
CEOs need to know what they should worry about

Nearly every week during the past few years has featured a headline about the latest data breach, malware attack, ransomware demand, or unrecoverable corporate data loss. Those stories are frequently followed by news that the CEOs at those companies were forced to resign.

Lee SavidgeCommented:
Your only other option is to have another column and use code to combine the content outside of the database.

So you'd have 2 nvarchar columns, one 4000 chars and the other 1000 chars.
25112Author Commented:
hmm.. does that mean SQL Server max char in one field/variable can never be more than 4000 (or 8000 for varchars).. so it is impossible to store a comment of 8001 characters in SQL database in one column, even in the latest version of sql?

if nvarchar(4000) is the same as nvarchar(max), why do we even have a MAX option? it does not give anymore than just the plain nvarchar(4000), does it?
Lee SavidgeCommented:
nvarchar(max) is similar to ntext. It is not the same as nvarchar(4000).

The 4000 char limit on nvarchars has been the same since it was brought in. The varchar field holds 8000 characters because it is not unicode compliant.
25112Author Commented:
>>nvarchar(max) is similar to ntext. It is not the same as nvarchar(4000).
I am sorry I don't get it, if it wont allow more than 8000 characters. can you give an example to show please...

please see below example: how can it store more than 8000 characters? but my ALTER statement fails?
(ALTER TABLE States ALTER COLUMN StateComment  nvarchar(5000) NULL )
create table String (String1 nVARCHAR(MAX))
set @i = 46000
set @String=''

WHILE @i>0
SELECT @String = @String + 'Apple!'
SET @i = @i - 1
insert into String select @string
SELECT LEN(@String) as Length
SELECT LEN(String1) from String as Length
drop table String
Lee SavidgeCommented:
If you want 5000 use the example found onthis site:

Create your table with your column in this case limited to 5000
CREATE TABLE VarChar5000 ( [VarChar5000] VARCHAR(MAX) )

    ADD CONSTRAINT [MaxLength5000]
    CHECK (DATALENGTH([VarChar5000]) <= 5000)

Open in new window

Now fill it with something as an example
INSERT INTO VarChar5000 ( [VarChar5000] )

Open in new window

Now select something
SELECT * FROM VarChar5000

Open in new window

There are limitations you should learn about when using nvarchar(max) column types.
25112Author Commented:
lsavidge, thanks- but that is varchar..  we need nvarchar in this application.. the data needs it.

can you comment on my example, how nvarchar was able to hold/store more than 8000 characters, but ALTER statement fails.
Scott PletcherSenior DBACommented:
In SQL Server, you cannot specify a fixed length for an nvarchar greater than 4000 ... period.

To store more than 4000 nvarchar characters in a single column, you must designate a column as nvarchar(max), which allows it to store just over 1 billion characters.
Lee SavidgeCommented:
Actually ScottPletcher, you can put a contraint on the field to prevent additional length on the nvarchar(max) field. It isn't ideal.

As for wanting nvarchar instead of varchar, seriously... just add an n in front of the data type declaration and then double the size of the constraint to allow for unicode.

create table [dbo].[nvarchar10000] ( [nvarchar10000] nvarchar(max) )

alter table [dbo].[nvarchar10000] 
    add constraint [maxlength10000]
    check (datalength([nvarchar10000]) <= 10000)

-- this will fail
insert into [dbo].[nvarchar10000] ( [nvarchar10000] )
values (replicate(cast('a' as nvarchar(max)), 5001))

-- this will work
insert into [dbo].[nvarchar10000] ( [nvarchar10000] )
values (replicate(cast('a' as nvarchar(max)), 5000))

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
25112Author Commented:
makes sense- thanks a lot!
Scott PletcherSenior DBACommented:
But you haven't specified a specific length greater than 4000 in the table definition.  You still designated the column as "nvarchar(max)", exactly as I stated.

Of course  you can use a constraint to restrict the length, as you could if you wanted by declaring a column as "varchar(200)" but never allowing more than 100 bytes into it.

But any code using that column is still dealing with a "max" data type, not a fixed-width data type; thus, all restrictions related to that still apply to that column.
Lee SavidgeCommented:
The request stated that 5000 chars would suffice. This works for the asker. It isn't ideal and I can't see the point of it being nvarchar(max) with a constraint as it is still nvarchar(max) internally, I agree.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.