May 27, 2008 at 12:03 am
Hi All..
[Code]
declare @C varchar(8000)
set @C = N'hello'
[/Code]
In the above statement there is the "N" mentioned in the beginning of the string.. what does it mean exactly???
Is it indirectly enforcing the casting of varchar into Nvarchar????
Can any one explain??
Thanks in advance..
Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################
May 27, 2008 at 12:57 am
Hi,
You can find this information about the N-prefix in the BOL:
Unicode string constants that appear in code executed on the server, such as in stored procedures and triggers, must be preceded by the capital letter N. This is true even if the column being referenced is already defined as Unicode. Without the N prefix, the string is converted to the default code page of the database. This may not recognize certain characters.
Hans
May 27, 2008 at 1:00 am
Where does it say anything about 4000?
May 27, 2008 at 1:12 am
The answer says it's cast as nvarchar(4000) initially, but i too dont see how?
Am i missing something, or is there a typo?
Matt
May 27, 2008 at 1:20 am
Because of the N-prefix the value is cast to NVarChar. The NVarChar(MAX) can contain a maximum of 4000.
BOL:
nvarchar [ ( n | max ) ]
Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for nvarchar are national char varying and national character varying.
May 27, 2008 at 2:08 am
Thankyou Hanshi.
Ive always wondered what the N was for. Thanks.
Matt.
May 27, 2008 at 2:29 am
Very good question 🙂
Tests number of areas of knowledge .. what something logically represents vs. what is physically represents
😎
May 27, 2008 at 6:07 am
Just to clarify HanShi's posting - NVARCHAR(MAX) is not limited to 4000 characters. The maximum length of VARCHAR(MAX) is 2^31 -1 (2,147,483,647) characters.
May 27, 2008 at 8:48 am
Uses per character 16 bits rather than 8.
May 27, 2008 at 10:24 am
1. The "N" prefix indicates "National" characters, that is, double-byte characters to support large-alphabet languages such as Japanese.
2. The concatenation of N'hello' to the repeated hyphens is where the first conversion to national characters happens.
3. Although a variable defined as varchar(max) or nvarchar(max) may hold many more characters, without the "max", the largest size is 8000 bytes (4000 double-byte characters).
4. The assignment of the concatenated string to @C converts the nvarchar data back to varchar, but it's already truncated at 4000 characters
Try running the code with different values to see either the truncation in effect or parsing errors on the maximum size of a data type.
Finishes with same output as original:
declare @C varchar(7000)
set @C = N'hello' + replicate('-',9000)
print len(@c)
print @C
Max size error:
declare @C varchar(9000)
set @C = N'hello' + replicate('-',9000)
print len(@c)
print @C
May 27, 2008 at 11:57 am
Aleksandr,
The truncation happens in the concatenation of the hyphens to N'hello', step 2 in my previous post. It's truncated because the string 'hello' is cast as National characters, so the concatenation is kept as double-bytes with a limit of 4000. This is before the string is assigned to the local variable, so even though @C can hold more, the 4000 characters is all that is sent to it.
Again, try it out.....
declare @C nvarchar(max)
set @C = Convert(nvarchar(max), N'hello') + replicate('-',9500)
print len(@c)
set @C = Convert(nvarchar(max), N'hello') + convert(nvarchar(max),replicate('-',9500))
print len(@c)
set @C = Convert(nvarchar(max), N'hello') + replicate(convert(nvarchar(max),'-'),9500)
print len(@c)
set @C = N'hello' + replicate(convert(nvarchar(max),'-'),9500)
print len(@c)
May 27, 2008 at 12:03 pm
Good Questions ............ 😛
May 27, 2008 at 12:51 pm
Aleksandr Furman (5/27/2008)
About size of nvarchar(max)I tried this:
declare @C nvarchar(max)set @C = N'hello' + replicate('-',8000)
print len(@c)
the result was still 4000. Can someone explain why it wasn't more since nvarchar(max) can accept more then 4000
The REPLICATE('-', 8000) has an implicit cast to NVARCHAR, try this to explicitly CAST it to NVARCHAR(MAX):
declare @C nvarchar(max)
set @C = N'hello' + CAST(replicate('-',8000) AS NVARCHAR(MAX))
print len(@c)
you get 8005 as the length.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply