Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

char and varchar! Expand / Collapse
Author
Message
Posted Wednesday, July 1, 2009 10:49 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 6:34 AM
Points: 654, Visits: 265
Comments posted to this topic are about the item char and varchar!
Post #745956
Posted Thursday, July 2, 2009 1:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 2,542, Visits: 2,410
This a question for very beginner!
Post #746038
Posted Thursday, July 2, 2009 3:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 2,880, Visits: 3,237
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #746079
Posted Thursday, July 2, 2009 3:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 2,542, Visits: 2,410
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 [ ]
Post #746100
Posted Thursday, July 2, 2009 4:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 6,020, Visits: 8,289
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!



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #746111
Posted Thursday, July 2, 2009 4:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 2,880, Visits: 3,237
Hugo, 'calculating index key size' in BOL disagrees with what you said. I hesitate to say which source is wrong

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #746117
Posted Thursday, July 2, 2009 4:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 6,020, Visits: 8,289
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).



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #746127
Posted Thursday, July 2, 2009 5:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 2,880, Visits: 3,237
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #746146
Posted Thursday, July 2, 2009 5:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 6,020, Visits: 8,289
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!



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #746155
Posted Thursday, July 2, 2009 7:27 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 8:35 AM
Points: 1,439, Visits: 8,173
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.




Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #746249
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse