What is DATALENGTH() in SQL Server?

In SQL Server (and Azure), the T-SQL DATALENGTH() function returns the number of bytes used to represent any expression.

For example:

SELECT DATALENGTH('Lit');

Result:

In this case, there are 3 bytes in the string Lit.

However, this is a simple example. The results can look a lot different, depending on the data type.

In the above example, we saw that there are 3 bytes in the word Lit. This also happens to be exactly how many characters are in the string. But if we return that same word from a column in a database, we might get a different result. For example, this:

SELECT 
    ArtistName, 
    DATALENGTH(ArtistName) AS DataLength 
FROM Artists 
WHERE ArtistName = 'Lit';

Result:

ArtistName  DataLength
----------  ----------
Lit         6

In this case the data length is 6.

The reason for this is because, in this particular database, the column has a nvarchar(255) data type. The nvarchar data type is a unicode string, which stores 2 bytes per character (which effectively doubles the data length). If the column had instead used varchar(255) it would have a data length of 3. However, if it had say, char(25) the string would have a data length of 25.

Also, if it had previously been char(25) but it was then converted to varchar(255) it would still have a data length of 255.

However, in all cases, the LEN() function would’ve returned 3.

Trailing Blanks

The DATALENGTH() function counts trailing blanks (e.g. spaces at the end of the string, tabs, carriage returns, etc).

Example:

SELECT DATALENGTH('Lit ');

Result:

In this example I simply added a space to the end of the string. This is another one of the differences between LEN() and DATALENGTH().

You can see examples of LEN() and DATALENGTH() side by side in LEN() vs DATALENGTH().

Return Type

The DATALENGTH() function returns a data type of int, unless the expression is varchar(max), nvarchar(max) or varbinary(max), in which case it returns bigint.