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),
thanks a lot in advance
@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.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis