CODEWITHSUNDEEP
sqlsql-servert-sql
Hernan Demczuk
Hernan Demczuk

Reputation: 405

DATALENGTH VARCHAR in SQL Server

According to MSDN the storage size of a VARCHAR data type is the actual length of the data entered + 2 bytes. I'm trying to understand why when I run this query:

DECLARE @VARCHAR VARCHAR(40) = 'RACING CLUB DE AVELLANEDA'
SELECT DATALENGTH(@VARCHAR) AS Bytes, LEN(@VARCHAR) AS Characters;

The storage size of @VARCHAR is the same as it was a CHAR data type, It's 25 and not 27 (25+2).

Does it has something to do with DATALENGTH function?

Upvotes: 2

Views: 3890

Answers (2)

sqluser
sqluser

Reputation: 5672

DATALENGTH returns the number of bytes used

LEN returns the number of characters used

Just compare these results

DECLARE @VARCHAR VARCHAR(40) = 'RACING CLUB DE AVELLANEDA'
SELECT DATALENGTH(@VARCHAR) AS Bytes, LEN(@VARCHAR) AS Characters;

Bytes: 25 ==> As it is a variable length, returns 25    
Characters: 25

DECLARE @VARCHAR CHAR(40) = 'RACING CLUB DE AVELLANEDA'
SELECT DATALENGTH(@VARCHAR) AS Bytes, LEN(@VARCHAR) AS Characters;

Bytes: 40 ==> As it is a fixed length, returns 40
Characters: 25

DECLARE @VARCHAR NVARCHAR(40) = 'RACING CLUB DE AVELLANEDA'
SELECT DATALENGTH(@VARCHAR) AS Bytes, LEN(@VARCHAR) AS Characters;

Bytes: 50 ==> As it is a variable length, returns 25*2 = 50
Characters: 25

DECLARE @VARCHAR NCHAR(40) = 'RACING CLUB DE AVELLANEDA'
SELECT DATALENGTH(@VARCHAR) AS Bytes, LEN(@VARCHAR) AS Characters;

Bytes: 80 ==> As it is a fixed length, returns 40*2 = 80
Characters: 25

Also you can easily add +2 to your DATALENGTH for VARCHAR data types

DECLARE @VARCHAR VARCHAR(40) = 'RACING CLUB DE AVELLANEDA'
SELECT  DATALENGTH(@VARCHAR) AS Bytes
        ,LEN(@VARCHAR) AS Characters
        ,DATALENGTH(@VARCHAR) + 2 AS ActualUsedBytes

Output

Bytes   Characters  ActualUsedBytes
25      25          27

Upvotes: 4

Scott C
Scott C

Reputation: 1660

datalength will tell you the number of bytes of data, which doesn't include the 2 bytes that precede that data in storage. The 2 byte overhead of a varchar is for recording the number of bytes in the string - i.e. in your case 25

Upvotes: 5

Related Questions