By: Daniel Calbimonte
The DATALENGTH function is used to show the number of bytes used to represent an expression. This is useful to see the space used for a column in a database table. The number returned by the function can be int or bigint.
Syntax
DATALENGTH(expression)
Parameters
- expression – String or expression, can be any data type.
Simple DATALENGTH Example
Below is a simple example using the AdventureWorks database with DATALENGTH. We will show the number of bytes used by a varbinary(max) column. We will measure the size of gif photos in bytes.
SELECT DATALENGTH([LargePhoto]) bytes,[LargePhotoFileName] FROM [Production].[ProductPhoto]
The query will show the bytes used per image.
DATALENGTH in KBytes Example
The next example will show the KBytes used per image.
SELECT DATALENGTH([LargePhoto])/1024.00 Kbytes,[LargePhotoFileName] FROM [Production].[ProductPhoto]
Here are the results.
DATALENGTH in MB Example
This example, will show the column size in MB.
SELECT DATALENGTH([LargePhoto])/1048576.00 Mbytes,[LargePhotoFileName] FROM [Production].[ProductPhoto]
The query will produce the following output.
Get SUM of DATALENGTH Example
The following shows how we can use the SUM function to get the overall space used per column using DATALENGTH.
SELECT SUM(DATALENGTH([LargePhoto])) as PhotoBytes, SUM(DATALENGTH([LargePhotoFileName])) as FilenameBytes FROM [Production].[ProductPhoto]
Bytes Used Per Data Type
The following example will return the bytes used per data type. This shows how much space the same value takes to be stored depending on the type of data type that is used.
DECLARE @myint int = 1 DECLARE @mysmallint smallint = 1 DECLARE @mydecimal decimal = 1 DECLARE @mybigint bigint = 1 SELECT 'int' datatype, DATALENGTH(@myint) bytes UNION SELECT 'smallint' datatype, DATALENGTH(@mysmallint) bytes UNION SELECT 'decimal' datatype, DATALENGTH(@mydecimal) bytes UNION SELECT 'bigint' datatype, DATALENGTH(@mybigint) bytes
The output is the following.
