25112
asked on
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).
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).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
So you'd have 2 nvarchar columns, one 4000 chars and the other 1000 chars.
ASKER
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?
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?
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.
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.
ASKER
>>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
-----------
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
-----------
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
can you comment on my example, how nvarchar was able to hold/store more than 8000 characters, but ALTER statement fails.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
makes sense- thanks a lot!
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.
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.
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.
ASKER