Recently I came across a situation where queries are loading extremely slow from a table. After careful analysis we found the root cause being, a column with ntext datatype was getting inserted with huge amounts of text content/data. In our case DATALENGTH T-SQL function came real handy to know the actual size of the data in this column.
According to books online, DATALENGTH (expression) returns the length of the expression in bytes (or) the number of bytes SQL needed to store the expression which can be of any data type. From my experience this comes very handy to calculate length and size especially for LOB data type columns (varchar, varbinary, text, image, nvarchar, and ntext) as they can store variable length data. So, unlike LEN function which only returns the number of characters, the DATALENGTH function returns the actual bytes needed for the expression.
Here is a small example:
Use AdventureWorksLT2012 GO Select ProductID, DATALENGTH(Name) AS SizeInBytes, LEN(Name) AS NumberOfCharacters FROM [SalesLT].[Product]
–Results
If your column/expression size is too large like in my case, you can replace DATALENGTH(Name) with DATALENGTH(Name)/1024 to convert to KB or with DATALENGTH(Name)/1048576 to get the size in MB.
Really usefull, thanks!!!
Sorry, its “useful”.
It is not working. Is showing same result for both function
SELECT i.ItemId,DATALENGTH(i.CompanyName) as Bytes, LEN(i.CompanyName) as Ch FROM Item i
1 13 13
2 13 13
3 13 13
7 13 13
Excellent. Thanks for the easy to understand explanation and the conversion to KB and MB.
I have noticed you don’t monetize dataginger.com, don’t waste your
traffic, you can earn extra cash every month. There
is one good way that brings decent money, you can google it: money making by bucksflooder
In the char data type, we are using only for length command in SQL server
's French winger Jonathan Biabiany on loan until the summer #CFC— Kaveh Solhekol (.SkyKaveh) January 30, 2017Oscar left for Shanghai SIPG at the end of December in a 锟?0m deal, leaving Conte with just Eden Hazard, Pedro and Willian able to play on the wing in his 3-4-3 formation.