July 1, 2009 at 10:49 pm
Comments posted to this topic are about the item char and varchar!
July 2, 2009 at 1:45 am
This a question for very beginner!
July 2, 2009 at 3:18 am
I got is right but imho all the answers as given are wrong.
Varchar does not just use the space required for the number of characters in the row. It is important to remember that varchar has a length prefix, and that a varchar field uses the data entered plus 2 bytes. I have seen some poor database designs that have ignored this fact, with varchar(1), varchar(2), etc fields.
Another important thing to remember is that varchar uses more space in an index than does a char column. Ther are also limitation about using varchar columns in an index if the varchar data has gone into overflow pages.
So, there is a lot more to the differences between char and varchar than allowed for in the answers in the question. If the question had included a 'none of the above' option I would have chosen that as the answer.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 2, 2009 at 3:51 am
EdVassie (7/2/2009)
I have seen some poor database designs that have ignored this fact, with varchar(1), varchar(2), etc fields.
Everybody always should use the RIGHT type.
varchar(1) and char(1) are not similar nor interchangeable.
Try this:
declare @vc varchar(1)
,@c char(1)
SET @vc = ''
SET @C = ''
print 'varchar[' + @vc + ']'
print 'char [' + @C + ']'
the result is very different:
varchar[]
char [ ]
July 2, 2009 at 4:16 am
EdVassie (7/2/2009)
Another important thing to remember is that varchar uses more space in an index than does a char column.
This is not correct. Space usage of char and varchar in indexes is equal as space usage of char and varchar in tables. So if a varchar saves space in a table, it saves space in an index as well.
I do agree with the rest of your post, though!
July 2, 2009 at 4:24 am
Hugo, 'calculating index key size' in BOL disagrees with what you said. I hesitate to say which source is wrong 🙂
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 2, 2009 at 4:45 am
Hi Ed,
I checked the BOL article you mention, but I failed to see any mention of varying length columns taking up more space. Maybe you can quote the relevant paragraph(s)? Also, what version of Books Online are you using? (Me, I'm using the April 2006 version of SQL 2005's BOL, but I also double-checked in the online SQL 2008 version of BOL at MSDN).
July 2, 2009 at 5:16 am
I'm sure I clicked in 'calculating index key size' when I looked at BOL, but the info is actually in 'Estimating the size of a nonclustered index'. I am using the SQL 2008 May 2009 BOL.
BOL is saying there is extra overhead if any varying length fields appear in an index, plus overhead for every varying length column.
Variable_Key_Size = 2 + (Num_Variable_Key_Cols x 2) + Max_Var_Key_Size
Max_Var_Key_Size also has its own calculation
The overhead is not much, but more than the 'correct' answer to the question implied.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 2, 2009 at 5:33 am
Hi Ed,
Your calculation is correct. And you'll find the exact same calculation in the topics on estimating the size of clustered indexes and heaps (except that in those, ALL columns are counted, not just those that are in the index). This confirms my point that space usage of varchar is the same in tables and indexes.
Note that this formula gives the maximum length. The actual length will be the actual amount of characters plus 2 (*). For non-Unicode characters, it's safe to say that if the average actual length is less than the maximum length by 2 or more characters, you'll save space in both table and indexes by using varchar rather than char.
(*) For simplicity sake, I leave out the extra 2 bytes of overhead for a row that includes any varying length columns, since they are not per column but per row.
Please note that I only disagreed with one specific part of your post, that seemed to imply that varchar takes more space in an index than it does in the table. I explicitly added that I do agree with the rest!
July 2, 2009 at 7:27 am
Carlo Romagnano (7/2/2009)
This a question for very beginner!
I agree, but at least 9 people (at the time of this posting) said there was no difference between the 2.
You'd be surprised how many people have been working with SQL Server for quite a while and still do not understand the difference.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 2, 2009 at 9:23 am
Very basic. But, still 16 people got it wrong as of now. Amazing. :hehe:
SQL DBA.
July 3, 2009 at 5:29 am
SanjayAttray (7/2/2009)
Very basic. But, still 16 people got it wrong as of now. Amazing. :hehe:
34 and counting...
November 24, 2009 at 12:02 am
Easy points for taking.
November 2, 2012 at 4:10 am
+1:) thanks
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply