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


Varchar or Char?


Varchar or Char?

Author
Message
Michael Poppers
Michael Poppers
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1103 Visits: 416
I thought the question was a fair one, especially as I'm in the minority of listmembers who chose the answer wisely :-), but I really wanted to say "Thank you" to Hugo et al. for a most-educational discussion!
Steve Jones
Steve Jones
SSC Guru
SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)

Group: Administrators
Points: 140523 Visits: 19415
Great discussion, and nice points (as always), Hugo!

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Robert Frasca
Robert Frasca
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1497 Visits: 794
1. The character set has already been defined in the question so regardless, nchar and nvarchar are out.

I see your point but I'm not really sure I agree with you. Just because he said it would be mixed letters and numbers doesn't mean that I won't need to process identifiers using letters and numbers from a different alphabet. My point is that he wasn't specific so a degree of ambiguity exists.

2. The average length isn't defined but it was a simple calculation. When building a database and constructing tables we rarely know what the contents of the table will be in advance. We therefore must assume they will be approximately equal if there is nothing to suggest the contrary.

He did say evenly distributed so I will retract my previous remark as that is pretty clear.

3. I take your point that varchar is more flexible and in future changes might make char less efficient, however at that point we're thinking outside the scope of the question. At the present time with the present facts, the choice is clear, however perhaps in a real setting you might choose differently given the purpose of the column



How is it that I'm thinking outside the scope of the question? My point is that he didn't clearly define the scope of what constituted "efficient" therefore any of the answers could be correct depending on your point of view. As you said, "perhaps in a real setting you might choose differently given the purpose of the column". Since he did specify that it was an "external identifier" I made the assumption that this identifier was the key to an external table. I don't want to have to use a TRIM function every time I refer to the column as that is decidedly NOT efficient and makes the queries less readable. It isn't that using a trim function is hard it just means that I have additional processing that must be performed with every row that is evalulated/returned. Not exactly my idea of efficient.

For me, efficiency is more about query performance and less about storage considerations. It's not that I discount the importance of storage considerations it's just that these considerations are less significant in this era of relatively cheap storage; however, they become more significant if I'm designing a multi-terabyte data warehouse for example instead of your run-of-the-mill 50 GB OLTP database. Consequently, the definition of "efficiency" may be vastly different.

"Beliefs" get in the way of learning.
paul.goldstraw
paul.goldstraw
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1486 Visits: 1765
Robert,

With regard to your first point, the question says

all English letters, both upper and lower case mixed with numeric digits

These are all a part of the standard ASCII character set and therefore would make the need for a unicode based string moot. If this were any other language then yes, you would need to use unicode but char will cut it in this scenario

As for the third point, yes, i agree. Coming more from the programmer side of the game rather than the administrative side, a couple of extra kb here and there and a few microseconds extra delay in reading it are worth it for the reduction in time taken to write the procedure. Anything that makes the code neater, easier to read and easier to write is fine by me, but I find these questions are rarely about the real world, rightly or wrongly. Don't forget almost all the questions on here are backed up by a link to BOL, hardly a source that takes into account the practical applications and is simply matter of fact about the information at hand. On this basis I guess you just have to ask yourself; what would Books Online say?
Rob Goddard
Rob Goddard
SSC Eights!
SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)

Group: General Forum Members
Points: 958 Visits: 558
Thanks Hugo, another gold nugget of SQL "under the hood".

Just thought I would add my thanks, and also tag this discussion as I dare say it will be invaluable in the future!

I must start reading Hugo's blog...

--------
I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams
Robert Frasca
Robert Frasca
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1497 Visits: 794
I guess I need to learn to read a little more carefully. My apologies. However, I must take one final whack at this dead horse. The fact that the identifiers have English characters doesn't mean that the app displaying these values isn't displaying them in another language. (I know, why would anyone display identifiers, but the fact is, they often do.)

I figured I would get the answer wrong because I do tend to think from a practical point of view and not based on a discussion of the internals in BOL. I also figured this debate would be interesting and I would learn something so I'm glad I got a chance to engage.

I suppose that if I want questions that are less ambiguous I should submit them myself. :-)

It's interesting to note that roughly 75% of us answered it "wrong" so perhaps questioning the scope of what constitutes "efficiency" isn't that far off base.

"Beliefs" get in the way of learning.
paul.goldstraw
paul.goldstraw
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1486 Visits: 1765
I'm glad you've found this conversation interesting; I have too Smile I tend to think these questions are like ones you get when you go for an interview for a DBA job or something, technically they're correct but their context in real life is lacking in the extreme and life is very rarely clear cut, unambiguous or pre-defined in its scope.

Even if you didn't read the question that clearly, are you really doing anything any one of us haven't done before? I can't remember the number of times i've misunderstood a specification or a request and come out with something completely wrong. I just take those lessons and move on hoping not to repeat them Smile
Steve Jones
Steve Jones
SSC Guru
SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)SSC Guru (140K reputation)

Group: Administrators
Points: 140523 Visits: 19415
Robert Frasca (10/16/2009)
I guess I need to learn to read a little more carefully. My apologies. However, I must take one final whack at this dead horse. The fact that the identifiers have English characters doesn't mean that the app displaying these values isn't displaying them in another language. (I know, why would anyone display identifiers, but the fact is, they often do.)


That's misreading the question. English characters in the db have nothing to do with the application. The idea is not for you to pick apart the question and look for an exception. It's to learn something about the simple case.

I would agree with you that the "efficient" work is misleading. It doesn't really define the criteria. I picked varchar, thinking that above 4-5 char, I just do that. I don't think there's been a reason in most apps to not pick it. There might be, and as Hugo mentioned, your app might require dealing with spaces, and then it would be important.

However with 1TB disk drives, and SQL reading in pages, I don't think the storage space, or performance is a big deal. It seems like it's a case of arguing if the Ferrari or the Lambrogini is a better sports car. They're both quick.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
AmolNaik
AmolNaik
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1627 Visits: 1234
I agree with cengland0.

Amol Naik
Tom Garth
Tom Garth
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1933 Visits: 1499
If no special criteria other than length is specified I usually select varchar (yes, I'm a programmer). However given the fact that the data was to be used as "identifiers", coupled with the relatively short length, I picked char. Indexed or not, the column is likely to be frequently used in where clauses. My instincts say that for those reasons, char will be more efficient.

I get the feeling that John derived this question from a real world application rather than just for kicks.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

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