The difference between the SQL Server function LEN and datalength

The datalength () function returns a number of bytes used to manage the value, which helps to reveal some interesting differences between different data types.

When the varchar type is passed to the datalength () and Len () functions, they return the same value:

DECLARE @Value varchar  SET @Value = ' abc ' SELECT datalength (@Value)  

The return values for these statements are 3.

Because the varchar type uses 3 single-byte characters to store a three-character value. However, if you use the nvarchar type to manage values of the same length, you need to take more than one byte:

DECLARE @Value nvarchar  SET @Value = ' abc ' Select DATALENGTH (@Value)  select LEN (@Value)

The datalength () function returns a value of 6 because each character that uses the Unicode character set consumes 2 bytes.

The LEN () function returns a value of 3 because the function returns the number of characters, not the number of bytes.

Here is an interesting test: How many bytes will be consumed to store an integer variable with a value of 2? And if you want to store an integer variable with a value of 2 billion, how many bytes will it occupy? Try it:

DECLARE @Value1 int, @Value2 int set @Value1 = 2  Set @Value2 = 2000000000  Select datalength (@Value1)  Select LE N (@Value1) Select  datalength (@Value2)  select LEN (@Value2)

In both cases, the DATALENGTH () function returns 4.

Because the int type, regardless of the value, always uses 4 bytes.

The LEN () function essentially treats integer values as data that has been converted to character, so in this case, it returns 1 and 10, which is the number of digits of the value.

Original: http://blog.csdn.net/Hello_World_wusu/article/details/4667452

The difference between the SQL Server function LEN and datalength