kapil190588 (10/5/2012)
hi,I am a newbie in filed of DBa. Can anyone plz explain me how the 41,80 coming while executing--
DECLARE @nvarchar nvarchar(40)
DECLARE @nchar nchar(40)
SET @nvarchar = '7 chars'
SET @nchar = '7 chars'
SELECT len(@nvarchar), len(@nchar),
len(@nvarchar+';'), len(@nchar+';'),
datalength(@nvarchar), datalength(@nchar)
Correct answer:
7,7,8,41,14,80
thanks a lot in advance
Yep.
@nchar has data type nchar(40). Nchar is fixed length, so after assigning '7 chars' to it, it will be padded out to 40 characters by adding 33 spaces at the end. The expression @nchar + ';' then adds a semicolon after the 33rd space (the 40th character) for a total of 41 characters. That's the explanation for the 41.
The result 80 is returned by datalength(@nchar). Remember that @nchar was padded to use all 40 characters. But the data type is nchar(40), not char(40). The difference is that nchar is able to store the full Unicode character set, at the expense of taking two bytes for each character. So to store 40 nchar characters, SQL Server uses 80 bytes.