Using Varchars … :) … October 10th, 2003

Vinod Kumar

I suppose we understand the difference in using Char and Varchars. Char’s do a padding and Varchars don’t pad the data before storing. Simple isn’t it …

Now for the actual interesting part. Look at the tables data I created.
Create table tempTable (v Varchar(10))
insert into tempTable  values (‘a’)
insert into tempTable  values (‘a ‘)
insert into tempTable  values (‘a  ‘)
Now I go ahead and execute the following query.
Select v, Len(v) from tempTable
What do you think is the output we are going to get … Are you disappointed to see the actual output? The mail is intended to target and make you aware of this behavior. Understand that “Len” would trim the trailing blank spaces and then take the length. Understand, we want you to take care of such behavior in your code. Now execute the next code:
Select v, DataLength(v) from tempTable
Now that is more like the output we actually expected … Please, Please be very careful while using “DataLength” function in the code. This actually returns the number of bytes used … So even though the output looked convincing to us the output would be wrong if it were to be NVarchar Columns … It would be double the value you expected.
And for others who think that the space is missing while storage also … Take another look. This is NOT the case. If we give an explicit space it is preserved while storing.
Select v + ‘|’ from tempTable
Be very careful in using the Len or DataLength keyword in SQL Server. Never assume the availability of a trailing blank. And this can change in any service pack that might come … 
Even the Group by function will eat up the trailing blanks before applying … This is by design …
Select Count(*), v from temptable
group by v


This entry was posted on Friday, October 10th, 2003 at 02:22 and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “Using Varchars … :) …”

  1. […] Now that we were all set with the concept, I pointed him to one of Vinod Kumar’s (blog|twitter) posts: […]

Leave a Reply