SQL Server DATALENGTH() Function

 The DATALENGTH() function in SQL Server is used to return the number of bytes used to represent a given expression. It is especially useful for determining the storage size of variable-length data types like VARCHAR, NVARCHAR, VARBINARY, and TEXT.

Syntax:

DATALENGTH(expression)

• expression: The expression whose byte size you want to determine. This can be a column, variable, or any valid expression.

Key Points:

1. The value returned by DATALENGTH() depends on the data type of the expression.

   • For VARCHAR or CHAR, it returns the number of characters (in bytes).
   • For NVARCHAR or NCHAR, it returns double the number of characters because each character uses 2 bytes.
   • For binary data types (VARBINARY, BLOB), it returns the exact byte count.

2. Unlike the LEN() function, which excludes trailing spaces for string data, DATALENGTH() includes all spaces.

3. It is commonly used to determine storage requirements or validate the length of variable-length fields.

Examples:

Example 1: String Data

SELECT DATALENGTH('Hello') AS ByteLength;

Output:

• 5 for VARCHAR
• 10 for NVARCHAR

Example 2: Trailing Spaces

SELECT DATALENGTH('Hello    ') AS ByteLength;

Output:

• 10 for VARCHAR (includes trailing spaces).

Example 3: NULL Value

SELECT DATALENGTH(NULL) AS ByteLength;

Output:

• NULL

Example 4: Binary Data

DECLARE @binaryData VARBINARY(MAX) = 0x123456;
SELECT DATALENGTH(@binaryData) AS ByteLength;

Output:

• 3 (since the data is 0x123456, which is 3 bytes).

Use Cases:

1. Storage Analysis: Check how much space specific data is consuming in the database.
2. Validation: Ensure data conforms to specific size constraints.
3. Performance Optimization: Understand the overhead caused by large variable-length fields.

Would you like an example applied to a specific table?