How to get length of Text, NText and Image columns in SQL Server

By: Greg Robidoux | Updated: 2022-04-04 | Comments (2) | Related: > Data Types

Problem

There is sometimes a need to figure out the maximum space that is being used by a particular column in your database. You would initially think that the LEN() function would allow you to do this, but this function does not work on Text, NText or Image data types, so how do you figure out the length of a value in a column that has one of these data types?

Solution

In addition to the LEN() function, SQL Server also has a DATALENGTH() function. This function can be used on all data types in your table.

Here is an example of running these commands on an IMAGE data type using the LEN() function:

SELECT name, LEN(packagedata) 
FROM msdb.dbo.sysssispackages

Here is an example of running these commands on an IMAGE data type using the DATALENGTH() function:

SELECT name, DATALENGTH(packagedata) 
FROM msdb.dbo.sysssispackages

If you wanted to find out the maximum length used for all of your records you could issue a command such as the following:

SELECT TOP 1 name, DATALENGTH(packagedata) 
FROM msdb.dbo.sysssispackages
ORDER BY 2 DESC

That's all there is to it. Another little function that you probably won't use often, but this is helpful to know it is there when you do need to use it.

Next Steps

Add this command to your SQL Server T-SQL toolbox
Take a look at these other useful T-SQL functions

About the author

Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-04-04

Comments For This Article

Wednesday, December 26, 2012 - 2:24:33 PM - Rodney

It is important to note that DATALENGTH returns the number of BYTES used to store the string INCLUDING any ending whitespace, while the LEN function returns the number of CHARACTERS in a string EXCLUDING any ending whitespace.

For example, if you insert the value 'TEST' into an NVARCHAR field, the LEN function returns 4 while the DATALENGTH function returns 8 (each character in an NVARCHAR field requires two bytes of storage). If you instead inserted the value 'TEST ' (with three spaces at the end of the string) the LEN function would still return 4 while the DATALENGTH function returns 14 (8 bytes for TEST plus 6 bytes for the three trailing spaces). Granted you still can't use the LEN function on large data types, but you should keep this difference in mind if you are planning to use the value in any string manipulations or character space calculations.

Friday, June 29, 2012 - 1:09:05 PM - John	Back To Top (18269)
Greg, Nice tip. Helped me out :-) John