SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


char and varchar!


char and varchar!

Author
Message
VM-723206
VM-723206
Right there with Babe
Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)

Group: General Forum Members
Points: 778 Visits: 267
Comments posted to this topic are about the item char and varchar!
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5071 Visits: 3327
This a question for very beginner!

I run on tuttopodismo
EdVassie
EdVassie
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5970 Visits: 3866
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: 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
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5071 Visits: 3327
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 [ ]

I run on tuttopodismo
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11236 Visits: 12005
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
EdVassie
EdVassie
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5970 Visits: 3866
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: 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
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11236 Visits: 12005
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
EdVassie
EdVassie
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5970 Visits: 3866
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: 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
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11236 Visits: 12005
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
Alvin Ramard
Alvin Ramard
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4273 Visits: 11639
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.

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search