Char(n) vs varchar(n) lengths

• gleb 43933

  Valued Member

  Points: 71

  More actions

  January 12, 2015 at 9:52 am

  #294193

  Hi experts,

  Could I please ask to clarify something:

  declare @Variable varchar(10)

  set @variable = 'ABCDE'

  Select @variable as VariableValue, DATALENGTH(@variable) as DATALENGTH, LEN(@variable) as LEN

  Gives the output:

  ABCDE - 5 - 5

  I thought varchar(n) stores the value for n+2 bytes. Am I wrong for expecting an output of "ABCDE 7 5" ? Does the DATALENGTH() ignore the additional 2-byte overhead of varchar ?

  Would appreciate some guidance

  Thanks

• Grant Fritchey

  SSC Guru

  Points: 398694

  More actions

  January 12, 2015 at 11:56 am

  #1769598

  You're measuring the length of the expression, not it's internal storage structures. So that's why it shows as 5.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Grant Fritchey

  SSC Guru

  Points: 398694

  More actions

  January 12, 2015 at 11:57 am

  #1769599

  And cross-posting doesn't help. We just get fragmented conversations.

  Please redirect further posts to here.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning