When working with data in SQL Server, it is important to understand the concept of length. SQL Server provides two system functions, Len() and DataLength(), that are used to measure the length of the data. While they may seem similar, there are key differences between the two.
Len() Function
The Len() function is used to get the string length of the data. It converts the input to a string and trims any trailing spaces. For example:
DECLARE @x VARCHAR(MAX), @x1 CHAR(10), @x2 INT
SELECT @x = 'abc', @x1 = 'abc', @x2 = 999999
SELECT LEN(@x) AS x, LEN(@x1) AS x1, LEN(@x2) AS x2
The result of this query would be:
x x1 x2
--- --- ----
3 3 6
As you can see, the Len() function returns the length of the string, trimming any trailing spaces.
DataLength() Function
The DataLength() function, on the other hand, measures the storage length of the data. It does not trim any trailing spaces. For example:
DECLARE @x3 NVARCHAR(20), @x4 VARBINARY(20)
SELECT @x3 = 'abc ', @x4 = 0x61626320
SELECT DATALENGTH(@x3) AS x3, DATALENGTH(@x4) AS x4
The result of this query would be:
x3 x4
--- ---
8 4
In this case, the DataLength() function returns the total storage length of the data, including any trailing spaces.
Key Differences
It is important to understand the key differences between Len() and DataLength(). Here are some important points to note:
- Len() converts the input to a string and trims any trailing spaces, while DataLength() measures the storage length without trimming.
- Len() may cause trouble if you mix the concepts of string length and storage length. For example, a fixed-length string will have trailing spaces trimmed by Len() but not by DataLength().
- DataLength() can be used with any data type, including sql_variant, CLR types, and XML, while Len() may return an error if the parameter cannot be explicitly converted to a string.
Conclusion
Understanding the differences between Len() and DataLength() is crucial when working with data in SQL Server. By using the appropriate function, you can accurately measure the length of your data and avoid any unexpected issues.
John Huang, SQL Server MCM + MVP
Source: http://sqlnotes.info