The SQL Server DATALENGTH is used to return the number of bytes used to represent a specified expression. The syntax of DATALENGTH is

SELECT DATALENGTH (String_Expression)
FROM [Source]

This SQL DATALENGTH function is useful for VARCHAR, NVARCHAR, VARBINARY, TEXT, NTEXT, and IMAGE Data Types because these data types will store the data length. For this example, We are going to use the below-shown table.

Employee Table Records 1


The DATALENGTH Function is used to return the number of bytes used to represent a given expression. The following query will show multiple ways to use it.

The first four lines of SQL Server code are used to declare the string variable of Data Type Varchar and Nvarchar and assign the same data to both variables.

In the below two statements, We used the SQL DATALENGTH function to find the data length of both variables. We also assigned a new name to that result as ‘Data Length’ using the ALIAS Column.

In the next line, We used it directly on the string.

SET @String_Exp = 'Learn SQL Server' 

SET @Str_Exp = 'Learn SQL Server' 
SELECT DATALENGTH(@String_Exp) AS 'Data Length' 
SELECT DATALENGTH(@Str_Exp) AS 'Data Length'
--Finding directly
SELECT DATALENGTH('SQL Server Tutorial') AS 'Data Length'
SELECT DATALENGTH('SQL Server') AS 'Data Length'

The SQL DATALENGTH function also allows you to find the number of bytes in a column. In this example, We are going to find the Data length of a Department Name and Email columns.

SELECT [FirstName]
      ,DATALENGTH([DepartmentName]) AS [Dept Data Length]
      ,DATALENGTH([Email]) AS [Email Data Length]
 FROM [Employe]

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.

Comments are closed.