DATALENGTH#
Returns the number of bytes used to represent an expression. Unlike LEN, DATALENGTH counts trailing blanks, and for Unicode data it returns two bytes per character. It returns NULL when the expression is NULL.
Syntax#
DATALENGTH ( expression )
Arguments#
- expression
An expression of any data type.
Return types#
int, or bigint when expression is of a large-value type (varchar(max), nvarchar(max) or varbinary(max)).
Remarks#
The byte length follows SQL Server rules: fixed-length char/nchar/binary return their declared length; variable-length varchar/varbinary return the number of bytes actually stored; nvarchar/nchar count two bytes per UTF-16 code unit (a supplementary character occupies four bytes); and decimal/numeric are sized by the number of significant digits of the value.
When the argument comes from a SQL Server source, DATALENGTH is evaluated on that source and is byte-exact for every type and collation. For data materialised in the Querona engine, non-Unicode (varchar) byte length is computed as one byte per character, which is exact for single-byte code pages.
For a sql_variant argument materialised in the engine, the byte length is derived from the value’s runtime type, because a boxed value no longer carries its original SQL base type. Strings inside a variant are counted as Unicode (two bytes per character), decimals by their significant digits, and other scalars by their fixed native width. This is exact for the common cases but approximate where the base type cannot be recovered — for example a varchar stored in a sql_variant reports two bytes per character. Push DATALENGTH down to a SQL Server source for byte-exact results on every base type.
Example#
SELECT DATALENGTH(N'abc') AS ByteLength; -- 6