char and varchar!

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by VM » char and varchar!

14 posts, Page 1 of 2

1 2 »»

char and varchar!

Rate Topic

Display Mode

Topic Options

Author

Message

VM-723206

Posted Wednesday, July 01, 2009 10:49 PM

SSChasing Mays

Group: General Forum Members
Last Login: Saturday, April 06, 2013 12:20 AM
Points: 649, Visits: 263

Comments posted to this topic are about the item char and varchar!

Post #745956

Carlo Romagnano

Posted Thursday, July 02, 2009 1:45 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:56 AM
Points: 1,972, Visits: 1,822

This a question for very beginner!

Post #746038

EdVassie

Posted Thursday, July 02, 2009 3:18 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:26 AM
Points: 2,621, Visits: 2,759

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.

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 25 March 2013: now over 23,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

Carlo Romagnano

Posted Thursday, July 02, 2009 3:51 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:56 AM
Points: 1,972, Visits: 1,822

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

Hugo Kornelis

Posted Thursday, July 02, 2009 4:16 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 5,244, Visits: 7,062

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

EdVassie

Posted Thursday, July 02, 2009 4:24 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:26 AM
Points: 2,621, Visits: 2,759

Hugo, 'calculating index key size' in BOL disagrees with what you said. I hesitate to say which source is wrong Smile

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 25 March 2013: now over 23,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

Hugo Kornelis

Posted Thursday, July 02, 2009 4:45 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 5,244, Visits: 7,062

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

EdVassie

Posted Thursday, July 02, 2009 5:16 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:26 AM
Points: 2,621, Visits: 2,759

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.

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 25 March 2013: now over 23,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

Hugo Kornelis

Posted Thursday, July 02, 2009 5:33 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 5,244, Visits: 7,062

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

Alvin Ramard

Posted Thursday, July 02, 2009 7:27 AM

Hall of Fame

Group: General Forum Members
Last Login: Saturday, November 10, 2012 8:24 AM
Points: 3,031, Visits: 7,383

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 »

14 posts, Page 1 of 2

1 2 »»

Permissions