DATALENGTH Function in SQL Server

by

In this SQL Server tutorial, I will teach you about the DATALENGTH function in SQL Server with different examples.

While working the database, such as retrieving information or performing any operations on the data table, for example, you may need to check the size of the information stored in the table’s column or increase the efficiency of the data retrieval or search process at the storage level.

At that time, consider using the DATALENGTH() function.

Additionally, you will learn where the DATALENGTH function in SQL Server can be useful and its syntax with definition.

DATALENGTH Function in SQL Server

The DATALENGTH() function in SQL Server returns the integer value, which is the bytes of the string. If you still don’t get that definition, let me explain it to you in a little simpler.

If you have a string and want to know the space consumed by this string in bytes, then the DATALENGTH() function returns the length of that string in bytes; that’s it.

The syntax is given below.

DATALENGTH(expression_value)

Where,

  • DATALENGTH(): It is the function that returns the length of the string in bytes.
  • expression: It is the string or column value whose length you want to get in the bytes. It supports different types of datatype such as nvarchar, varchar, image, text, varbinary, and binary.

Now you know the purpose of the DATALENGHT(), maybe you are thinking about where this function can be helpful; you can use this function to know the actual space taken by the string in your memory, which is necessary for an efficient database design.

Also, knowing the string length in bytes helps in data analysis, storage management, and performance tuning. Check out the LEN() function in SQL Server.

But remember, if you pass the null expression or value to the DATALENGTH() function, then in that case, it returns the NULL value instead of the bytes.

For example, pass the string ‘United States’ to the DATALENGTH() function and find the length bytes using the below query.

SELECT DATALENGTH('United States') LengthInBytes;
DATALENGTH Function in SQL Server Returning String Length in Bytes

From the above output, the length of the string ‘United States’ in bytes is 13, as you can see in the result set of the above query. Also, just so you know, the DATALENGTH() function returns the length string in bytes, including the spaces, whether they are leading or trailing spaces.

This is how you can use the DATALENGTH function in SQL Server to find the string length in bytes.

DATALENGTH Function in SQL Server on Datatype

In this section, you will know the bytes of each datatype, such as varchar, nvarchar, int, bigint, etc.

First, let’s know the length of the varchar datatype in bytes using the below query.

DECLARE @varcharlength varchar = 1;
SELECT DATALENGTH(@varcharlength) LengthInBytes;
DATALENGTH Function in SQL Server on VARCHAR Datatype

The length of the varchar datatype is 1, as you can see in the above output, which means it takes one byte of space to represent a value in the memory.

Next, let’s see the nvarchar datatype using the below query.

DECLARE @nvarcharlength nvarchar = 1;
SELECT DATALENGTH(@nvarcharlength) LengthInBytes;
DATALENGTH Function in SQL Server on NVARCHAR Datatype

As you can see, the length of the nvarchar datatype in byte is 2.

Check the length of the int datatype in bytes using the below query.

DECLARE @intlength int = 1;
SELECT DATALENGTH(@intlength) LengthInBytes;
DATALENGTH Function in SQL Server on INT Datatype

As you can see, the int data type length in bytes is 4, which means it takes 4 bytes in memory.

In the same way, you can find the length of all the datatype in bytes using the DATALEnTH() function.

This is how to find the datatype length in bytes using the DATALENGTH function in SQL Server.

DATALENGTH Function in SQL Server on Table

Let’s see how to use the DATALENGTH() function on the table; for example, you have an Employee table with columns EmployeeID, FirstName, and LastName, as shown below.

DATALENGTH Function in SQL Server on Table Employees

Using the above table, you need to find the length in bytes of the employee’s first name. For that, use the query below.

SELECT FirstName, DATALENGTH(FirstName) AS Bytes FROM Employees;
DATALENGTH Function in SQL Server on Table

Look at the output of the above query. The result set contains two columns, FirstName and the Bytes. FirstName shows each employee’s first name, and the Bytes contain the space taken by each employee’s first name in the memory in bytes.

For example, the employee with the first name Ronan has a length of 10 bytes. Using this concept, you can also limit the size of the first name in bytes for the employees. This is just an example, but in real life, you may use the DATALENGTH() function differently as the requirement arises.

Conclusion

In this SQL Server tutorial, you learned how to find the string length or other datatype in bytes using the DATALENGTH function in SQL Server. You also applied the DATALENGTH() function on the table column and returned the length in bytes of each value.

You may like to read: