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 «««23456»»»

Varchar or Char? Expand / Collapse
Author
Message
Posted Friday, October 16, 2009 8:53 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:58 AM
Points: 643, Visits: 400
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!
Post #804269
Posted Friday, October 16, 2009 8:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:35 PM
Points: 33,099, Visits: 15,207
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
Post #804273
Posted Friday, October 16, 2009 9:14 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 5, 2014 1:57 PM
Points: 545, Visits: 643
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.
Post #804295
Posted Friday, October 16, 2009 9:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:47 AM
Points: 1,030, Visits: 1,395
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?
Post #804306
Posted Friday, October 16, 2009 9:33 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, May 24, 2013 5:20 AM
Points: 660, 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
Post #804311
Posted Friday, October 16, 2009 9:47 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 5, 2014 1:57 PM
Points: 545, Visits: 643
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.
Post #804323
Posted Friday, October 16, 2009 9:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:47 AM
Points: 1,030, Visits: 1,395
I'm glad you've found this conversation interesting; I have too :) 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 :)
Post #804330
Posted Friday, October 16, 2009 10:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:35 PM
Points: 33,099, Visits: 15,207
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
Post #804343
Posted Friday, October 16, 2009 10:55 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:43 PM
Points: 945, Visits: 1,234
I agree with cengland0.

Amol Naik
Post #804375
Posted Friday, October 16, 2009 12:29 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #804438
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse