Post reply

varchar and nvarchar datalengths

  • June 25, 2012 at 2:23 pm

    #260485

    I'm finding an inconsistency with datalengths from MSDN and Datalength. When I run select datalength(column1) where the column is a varchar(10) with varying value lengths, I get those lengths. But from http://msdn.microsoft.com/en-us/library/ms176089.aspx they say I should get the different value lengths + 2. Which is it?

    There is an exception to every rule, except this one...

  • June 25, 2012 at 2:36 pm

    #1505273

    The +2 is the size of the column offset pointer, it's not reflected in the value returned from datalength.

    A varchar(10) that stores 8 characters takes 10 bytes of storage total (8 bytes for the data and 2 for the offset), datalength will return 8

    An nvarchar(10) that stores 8 characters takes 18 bytes of storage total (16 bytes for the data and 2 for the offset), datalength will return 16.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 25, 2012 at 2:38 pm

    #1505275

    Got it. Thanks for making sense of it for me.

    There is an exception to every rule, except this one...

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply