How long is your data?
No. It isn’t just the way you use your data that counts, the data size counts too
Ok, let’s get talk a little about some SQL Server basics. Specifically a couple of useful T-SQL functions.
Let’s talk about determining the ‘size’ of your data in a field.
There are 2 functions in SQL Server to help with this.
LEN() & DATALENGTH()
You may think these are interchangeable, but be aware that they are not. The first, LEN(), is used to measure the length of the data in a field.
DATALENGTH() is used to determine just how much space the data takes up in the database.
Len()
The easiest way to remember how LEN() works is with the following phrase:
‘When I, as a human, want to see the data, how many characters does this data take up?’
Now, there is a catch to eh LEN() function. It does count all the characters, but if you have empty space characters at the end of your string, they are not counted.
This can lead you to believe a field is storing just as you expect, but in actuality it could be padded out with extra spaces that LEN() isn’t catching.
This may be why you are not matching your query strings all the time.
[icon_box icon=”lightbulb-o” title=”What about using RTRIM() and LTRIM()”] LTRIM() and RTRIM() are 2 functions within SQL Server than can be used to trim leading (LTRIM()) and trailing (RTRIM()) empty space characters from a field. They only remove space characters though, so if there is some other strange character in the field that does not display, like a carriage return, you may still get incorrect matches. [/icon_box]DATALENGTH()
DATALENGTH()’s phrase to remember it is a little different:
“How much space to I need to store this data in the database, even the parts I can’t see?”
The DATALENGTH() function looks at the data you have and returns the amount of space being used to store that piece of data.
There are some times with varchar, that this value will match up with the LEN() value returned, but that is not always the case.
DATALENGTH() does not ignore empty space, or padding, or special characters. It is a measure of truly how much space is being used.
It is a great way to see if there are hidden characters in a string you may not catch with LEN().
I have a very simple script here that you can use to play with these 2 functions and have fun checking out the results.
DECLARE @lengthtest VARCHAR(15) -- Plain text SET @lengthtest = 'testing' SELECT @lengthtest, LEN(@lengthtest), DATALENGTH(@lengthtest) -- Add 3 empty spaces SET @lengthtest = 'testing ' SELECT @lengthtest, LEN(@lengthtest), DATALENGTH(@lengthtest) -- Add a special character - ® SET @lengthtest = 'testing'+char(0174) SELECT @lengthtest, LEN(@lengthtest), DATALENGTH(@lengthtest) -- Add a non-visible special character, a carriage return for example SET @lengthtest = 'testing'+CHAR(13) SELECT @lengthtest, LEN(@lengthtest), DATALENGTH(@lengthtest) -- Remember, datatype matter too! NVarchar stores each character in 2 bytes rather than 1, so your DATALENGTH() will usually be double the LEN() value DECLARE @Nlengthtest NVARCHAR(15) -- Plain unicode text SET @Nlengthtest = N'testing' SELECT @Nlengthtest, LEN(@Nlengthtest), DATALENGTH(@Nlengthtest) -- Both functions will also work with other data types, like integers! DECLARE @Ilengthtest int SET @Ilengthtest = 1234567 SELECT @Ilengthtest, LEN(@Ilengthtest), DATALENGTH(@Ilengthtest) -- Note the LEN() value is the actual length of the number, as we see it, but the DATALENGTH() value is how much space is being used to store the integer. -- 4 Bytes regardless of the integer value.
If you ran the above script (Check it yourself before you run any script you get online!) you would have seen some fun results. Things like how a NVarchar field takes up twice as much space and characters present. This is because storing Unicode values takes more space. You would also have seen that a numeric value stored in a a numeric datatype can also have the LEN() and DATALENGTH() functions applied to them.
You can play some more with the various data types and see how the result change.
You can get more detailed information about the data types that SQL Server supports here: https://msdn.microsoft.com/en-us/library/ms187752.aspx
[divider_top][icon_box icon=”users” title=”Who is Chris Bell?”]
Chris Bell, SQL Server MVP, MCSE & MCITP, is a 20 year veteran of using Microsoft products & SQL Server to create solutions for businesses, organizations and individuals. Chris speaks, blogs, writes articles and makes media of all kinds regarding SQL Server at WaterOxConsulting.com. Chris is also the founder of WaterOx Consulting, Inc. which features SQL Server consulting services along with the destination location week-long training series: SQL Summer Camp.
Click here to contact Chris or to follow him on twitter. [/icon_box]