SQL Server中的DATALENGTH VARCHAR

Question

根据MSDN，VARCHAR数据类型的存储大小是输入+2字节的数据的实际长度。我试图理解为什么当我运行这个查询时：

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

@VARCHAR的存储大小与CHAR数据类型相同，为25，而不是27 (25+2)。

它与DATALENGTH函数有关吗？

Answer 1

datalength将告诉您数据的字节数，它不包括存储中数据之前的2个字节。varchar的2字节开销用于记录字符串中的字节数(在您的示例中为25 )。

Answer 2

DATALENGTH返回bytes used的数量

LEN返回characters used的数量

只是比较一下这些结果

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

此外，还可以轻松地将+2添加到DATALENGTH中，用于VARCHAR数据类型。

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

输出

Bytes   Characters  ActualUsedBytes
25      25          27