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’)
GO

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.