Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase «««12345

String Length Expand / Collapse
Posted Friday, October 5, 2012 7:13 AM



Group: General Forum Members
Last Login: Yesterday @ 1:48 PM
Points: 7,765, Visits: 11,376
kapil190588 (10/5/2012)
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:

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 MVP
Visit my SQL Server blog:
Post #1369013
Posted Friday, October 5, 2012 7:18 AM



Group: General Forum Members
Last Login: Tuesday, November 8, 2016 4:31 AM
Points: 2,222, Visits: 2,760
thanks a lot Hugo it clears to me now!!

To get quick answer follow this link:
Post #1369018
Posted Thursday, May 30, 2013 3:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 20, 2016 5:11 AM
Points: 307, Visits: 675
Got it right 7,7,8,41,14,80
but thought 7,7,8,8,14,80

but that was`t in ans

Neeraj Prasad Sharma
Sql Server Tutorials
Post #1458100
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse