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
Greg Robidoux has been working with databases for 35+ years with extensive hands on SQL Server experience from version 6.5 to 2025. He has authored over 250 technical articles and delivered several presentations online and at various conventions. Greg is also the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server.
