Solved

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

Posted on 2013-11-12
14
4,141 Views
Last Modified: 2013-11-13
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).
0
Comment
Question by:25112
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 5

Author Comment

by:25112
ID: 39642093
i dont want to do a nvarchar(max).. but nvarchar(5000) would suffice. nvarchar(4000)  wont be enough.
0
 
LVL 9

Assisted Solution

by:dustock
dustock earned 100 total points
ID: 39642124
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.
0
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 300 total points
ID: 39642131
nvarchar(max) is your only option

Why don't you want to use one?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39642136
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.
0
 
LVL 5

Author Comment

by:25112
ID: 39642142
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?
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39642182
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.
0
 
LVL 5

Author Comment

by:25112
ID: 39642214
>>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))
DECLARE @String nVARCHAR(MAX)
DECLARE @i INT
set @i = 46000
set @String=''

WHILE @i>0
BEGIN
SELECT @String = @String + 'Apple!'
SET @i = @i - 1
END
insert into String select @string
SELECT LEN(@String) as Length
SELECT LEN(String1) from String as Length
drop table String
-----------
0
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 300 total points
ID: 39642301
If you want 5000 use the example found onthis site:

http://www.sql-server-helper.com/faq/sql-server-2005-varchar-max-p01.aspx

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

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

Open in new window


Now fill it with something as an example
INSERT INTO VarChar5000 ( [VarChar5000] )
VALUES (REPLICATE(CAST('A' AS VARCHAR(MAX)), 5000))

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.
0
 
LVL 5

Author Comment

by:25112
ID: 39642415
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.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 39642448
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.
0
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 300 total points
ID: 39643994
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) )
go

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

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

0
 
LVL 5

Author Comment

by:25112
ID: 39644810
makes sense- thanks a lot!
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39645603
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.
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39645620
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.
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question