DATALENGTH Functions in SQL Server

DATALENGTH is a function that returns the number of bytes used to represent any expression. This looks similar to LEN function that helps in identifying the length of the string, however DATALENGTH returns size in bytes for a given string expression. These outputs may differ depending on the data type and type of encoding used in the column.

Let’s see with an example.

CREATE TABLE [dbo].[tbStudentDetails]
([StudentID] INT IDENTITY(1,2),
 [StudentName] VARCHAR(50) NULL

INSERT [dbo].[tbStudentDetails] VALUES
(‘Zafar Iqbal’),(‘Tahir Iqbal’),(‘Danial Hussain’)

SELECT * FROM tbStudentDetails
This returns the following output.
1  Zafar Iqbal
3  Tahir Iqbal
5  Danial Hussain

Let’s see the difference between LEN and DATALENGTH functions.

SELECT LEN(StudentName), DATALENGTH(StudentName) FROM tbStudentDetails
SELECT LEN(StudentID), DATALENGTH(StudentID) FROM tbStudentDetails


As stated earlier, LEN function returned the length of the string however DATALENGTH returned the size in bytes for the given string expression.

Hope you find this article helpful.

Please subscribe for more interesting updates.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s