Comparing COL_LENGTH() and DATALENGTH() in SQL Server

by

SQL Server has a COL_LENGTH() function and a DATALENGTH() function that could easily be confused for doing the same thing. They both have “length” in their name, and they do indeed return a “length”. But the length returned is different for each function.

If you’ve ever wondered why DATALENGTH() gives you different numbers than COL_LENGTH(), read on to find out.

What COL_LENGTH() Does

COL_LENGTH() is all about metadata. It tells you the defined length of a column as stored in SQL Server’s schema. In other words, it doesn’t care about what’s actually inside a row – it only cares about how the column was declared. For example, if you create a column as VARCHAR(100), the COL_LENGTH() function will return 100, even if you’ve only inserted the string “abc” into that column.

Main points:

  • Returns the defined length of a column (in bytes).
  • Doesn’t depend on the data stored.
  • Useful for inspecting table design programmatically.

What DATALENGTH() Does

DATALENGTH(), on the other hand, looks at the actual data in a row and returns the number of bytes used to store it. For fixed-length types (like CHAR(10)), it will return the full size no matter what you store. For variable-length types (VARCHAR, VARBINARY, etc.), it will return the number of bytes actually occupied by the value.

Main points:

  • Returns the number of bytes required to store a value.
  • Varies with the data stored.
  • Great for troubleshooting storage or looking at how much space variable-length values consume.

Example

Here’s an example that shows the difference clearly. You can run this in a test database to test it yourself.

Let’s start with sample data:

CREATE TABLE DataAudit (
    AuditID INT IDENTITY PRIMARY KEY,
    UserCode CHAR(10),
    ActionDescription VARCHAR(50),
    Notes NVARCHAR(100)
);

INSERT INTO DataAudit (UserCode, ActionDescription, Notes)
VALUES
    ('AB1', 'Login', N'User successfully logged in'),
    ('LONGCODE99', 'FileUpload', N'Uploaded a 25MB report'),
    ('X', 'Delete', N'Deleted record 105 from archive');

Now we can query that table with both COL_LENGTH() and DATALENGTH().

Let’s start with COL_LENGTH():

SELECT
    COL_LENGTH('DataAudit', 'UserCode') AS UserCode_ColLength,
    COL_LENGTH('DataAudit', 'ActionDescription') AS ActionDescription_ColLength,
    COL_LENGTH('DataAudit', 'Notes') AS Notes_ColLength;

Output:

UserCode_ColLength  ActionDescription_ColLength  Notes_ColLength
------------------  ---------------------------  ---------------
10                  50                           200

These are the metadata-defined column lengths that we defined in our CREATE TABLE statement.

Now for DATALENGTH(): 

SELECT
    AuditID,
    DATALENGTH(UserCode) AS UserCode_DataLength,
    DATALENGTH(ActionDescription) AS ActionDescription_DataLength,
    DATALENGTH(Notes) AS Notes_DataLength
FROM DataAudit;

Output:

AuditID  UserCode_DataLength  ActionDescription_DataLength  Notes_DataLength
-------  -------------------  ----------------------------  ----------------
1        10                   5                             54              
2        10                   10                            44              
3        10                   6                             62

This is the actual storage per row.

We can see that the results for ActionDescription and Notes columns vary with each row. That’s because the data length varies with each row, and both columns are defined with a variable length data type (VARCHAR and NVARCHAR).

However, all UserCode values are 10 bytes, even though the strings appear to have different lengths across the rows. This is because that column was defined as CHAR(10), which makes it a fixed length. Any data that’s shorter is padded to make up the 10 bytes.

Just to ram the point home, let’s put COL_LENGTH() and DATALENGTH() in the same query, against the same column:

SELECT
    AuditID,
    COL_LENGTH('DataAudit', 'ActionDescription') AS ColLength,
    DATALENGTH(ActionDescription) AS DataLength
FROM DataAudit;

Output:

AuditID  ColLength  DataLength
-------  ---------  ----------
1        50         5         
2        50         10        
3        50         6

So in this case we analyzed the ActionDescription column. We can see that the column length is 50 but the data length varies with each row.

So that pretty much sums up the difference between the COL_LENGTH() and DATALENGTH() functions in SQL Server. SQL Server also has a LEN() function. See LEN() vs DATALENGTH() in SQL Server for the difference between these two functions.