InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 22:04:06 GMT20http://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxRealy good Question keep it up John :-)Mon, 15 Oct 2012 23:46:03 GMTSamith Chttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxWe need to define a column that will contain externally provided identifiers. Their (1) length will vary from seven to ten characters(2) all English letters(3) both upper and lower case mixed with numeric digits(4) with an even distribution of lengths <--missed this point when answering, thus got it wrong [I said varchar(10)]Which of these data types will be more efficient?Correct answer nvarchar(25)...Because...(1) I don't care how many people you asked in management, this will change next week as soon as this is in PROD...you know that's true(2) for now, but when you least suspect it/expect it you will have worry about localization...(3) anh, moot point...see point 2(4) unless this is a lookup table, this can only be the case if the number of records is evenly divisible by four, as you add one record at a time...unless all your batch inserts are also divisible by four and only then do you insert.good question though, since the parameters were set as hypotheticals, but in the 'real' world those first set of constraints ALWAYS are wrong...LOLMon, 02 Nov 2009 16:02:59 GMTmacrostarrphishhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxYeah thought so...I got it wrong because like a lot of you i chose varcharIts variable length data so and will need trimming before it can be used if you use char. The question "which is more efficient..." extends beyond the size of the type. without a bit more background on usage i find the question ambiguousIf you said to me a billion rows worked with one at a time, no question, storage is an issue and single rows won't take much trimming so char would be best. But if you said 10,000 rows used widely in joins for many operations then storage is less of an issue and repeatedly trimming spaces is more of an issue.Wed, 21 Oct 2009 09:22:29 GMTdavid.murdenhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspx[quote][b]Fatal Exception Error (10/19/2009)[/b][hr]How efficient will char be if you have throw an rtrim() in the where clause?[/quote]Not as efficient, but why would you want to use RTRIM in a WHERE clause? Even if you are restricting by length, SQL Server won't be counting the built in padding.Mon, 19 Oct 2009 07:14:11 GMTTom Garthhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxHow efficient will char be if you have throw an rtrim() in the where clause?Mon, 19 Oct 2009 06:40:18 GMTFatal Exception Errorhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspx[quote][b]john.arnott (10/16/2009)[/b][hr]Yes, hard drives now cost in the range of a dollar a GB, but the line of business I support has to pay the enterprise infrastructure division quite a bit more than than on an ongoing basis as a pro-rated share of support services. I only wish I could say "go down to the nearest Best-Buy and pick up a couple of 2TB USB drives". Nope. In this (and I suppose most large corporations), managed storage is priced to the user based on the full estimated cost of ownership. [/quote]The hard drive storage is quite expensive when you're looking at Enterprise systems. We use SANS drives with fiber channel drives with redundant drives. Also, they are backed up on tape and sent off-site. The cost of these systems are extremely expensive. When we ask for space, they generally give us 50 Gigs max. If we want more, it requires an act of congress. Every byte makes a difference. The log files that are generated by MSSQL is more annoying than the space used by the database itself. Those need to be backed up and then a shrink performed to recover that space. This needs to be done several times throughout the day since we have massive data loads and that increases the log files significantly.Fri, 16 Oct 2009 16:15:07 GMTcengland0http://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspx[quote][b]hawaiianrebel (10/16/2009)[/b][hr]See I got it wrong for two reasons:I was a knuckle head and missed the "English only" so yes varchar or char. The choice between the two though, to me, is subjective. Yes VARCHAR's do require an additional 2 bytes as length identifiers (remember VSAM? lol). I question is "Their length will vary from seven to ten characters" ... "with an even distribution of lengths" "Since the average size of the data is 8.5". We can't store data in half bytes (unless you are working with assembler. remember 3270 ASM?). In fact we can't store data in perfect little bytes. There are 32 or 64 bit WORD boundaries. For me 8, 12, or 16 would have been more efficient, but that's just the hardware nut in me.[/quote]No, we can't store data in half bytes. But when we multiple units of data, and not all data uses the same number of bytes, we can get fractional bytes for the [b]average[/b] unit. The simples example would be two rows, one with 10 bytes and the other with 11 bytes. That means 21 bytes for 2 rows, or an average of 10.5 bytes per row.The 32 or 64 bit word boundaries are completely irrelevant in SQL Server. If a row takes 1 byte less, 1 byte less is used. No 16-bit, 32-bit, or 64-bit aligning is done.Fri, 16 Oct 2009 15:09:52 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxSee I got it wrong for two reasons:I was a knuckle head and missed the "English only" so yes varchar or char. The choice between the two though, to me, is subjective. Yes VARCHAR's do require an additional 2 bytes as length identifiers (remember VSAM? lol). I question is "Their length will vary from seven to ten characters" ... "with an even distribution of lengths" "Since the average size of the data is 8.5". We can't store data in half bytes (unless you are working with assembler. remember 3270 ASM?). In fact we can't store data in perfect little bytes. There are 32 or 64 bit WORD boundaries. For me 8, 12, or 16 would have been more efficient, but that's just the hardware nut in me.Fri, 16 Oct 2009 14:55:53 GMThawaiianrebelhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxYes, hard drives now cost in the range of a dollar a GB, but the line of business I support has to pay the enterprise infrastructure division quite a bit more than than on an ongoing basis as a pro-rated share of support services. I only wish I could say "go down to the nearest Best-Buy and pick up a couple of 2TB USB drives". Nope. In this (and I suppose most large corporations), managed storage is priced to the user based on the full estimated cost of ownership. Fri, 16 Oct 2009 14:03:39 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspx[quote][b]john.arnott (10/16/2009)[/b][hr][quote][b]Tom Garth (10/16/2009)[/b][hr].....I get the feeling that John derived this question from a real world application rather than just for kicks.[/quote]Actually, yes. I've been going through a database structure provided by a vendor that sells their software globally. We use it only in the USA with English, and we've found that we would be able to cut the size of our databases by a substantial amount by changing many of the nchar() columns to varchar(). There are a few of those columns that vary in length, but only slightly, and use most of the vendor-allocated column length, so they will be changed from nchar() to char(), and not varchar().[/quote]This is largely off topic but I found this post interesting.Just out of curiosity, do you need to cut the size of your database substantially or does it just offend you that so much space is theoretically wasted? I can't help but wonder if the cost of changing the data types can be recovered by the savings gleaned from shrinking your databases? (Since the cost of storage is significantly less than a dollar a GB you'll need some pretty serious space savings to make it worth your while.) While it's true you might be able to fit a few more rows on each page, quantifying performance gains gleaned from shrinking the database is a little harder to do but is almost certainly measured in microseconds and the loss of scalability into foreign markets is a fairly significant trade off.I don't know whether you've done this or not but storing your data in unicode (as English) and then displaying it in your apps using another language is really pretty easy but if your data isn't in unicode you're seriously screwed should that requirement suddenly surface. The world is flat and unicode is a fairly insignificant cost of doing business. Clearly, your vendor understands this.Fri, 16 Oct 2009 13:41:15 GMTRobert Frascahttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspx[quote][b]Tom Garth (10/16/2009)[/b][hr].....I get the feeling that John derived this question from a real world application rather than just for kicks.[/quote]Actually, yes. I've been going through a database structure provided by a vendor that sells their software globally. We use it only in the USA with English, and we've found that we would be able to cut the size of our databases by a substantial amount by changing many of the nchar() columns to varchar(). There are a few of those columns that vary in length, but only slightly, and use most of the vendor-allocated column length, so they will be changed from nchar() to char(), and not varchar().Fri, 16 Oct 2009 12:48:10 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxWow. Amazing amount of information in this discussion, folks. I thank you all, especially those who pointed out alternate takes on the question and explanation.Yes, when formulating the question, I was thinking only of disk space used when I said "efficient", so please accept my apologies for that bit of ambiguity. I do thank Hugo for stepping in with his disection of the various issues raised -- I've learned a lot from that discussion.Also, as Steve alludes to in his post a couple ahead of this one, generally I've found that the QOD is focused on an idealized case to emphasize a particular point. The point I was thinking of here is that sometimes what appears to be an obvious choice may not be what was intended. In this case, if you really wanted to save every possible byte of storage, then char(10) would be better than varchar(10). Yes, in the 'real world' most of the time varchar(10) may be just as good in a practical sense, and would provide a bit more flexibity for possible future requirements changes, but this is QOD-land, not the real world. One more thing: I wouldn't have guessed that the 57% of respondants who chose varchar(10) were only interpreting "efficient" as having a broader meaning that included CPU usage and so on, but that most of them didn't work out the actual disk space usage to see that varchar(10) requires an average of a half byte more than char(10). Fri, 16 Oct 2009 12:38:04 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxIf 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.Fri, 16 Oct 2009 12:29:49 GMTTom Garthhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxI agree with cengland0.Fri, 16 Oct 2009 10:55:55 GMTAmolNaikhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspx[quote][b]Robert Frasca (10/16/2009)[/b][hr]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.)[/quote]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.Fri, 16 Oct 2009 10:13:56 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxI'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 :)Fri, 16 Oct 2009 09:54:46 GMTpaul.goldstrawhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxI 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.Fri, 16 Oct 2009 09:47:01 GMTRobert Frascahttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxThanks 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...Fri, 16 Oct 2009 09:33:31 GMTRob Goddardhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxRobert,With regard to your first point, the question says [b]all English letters, both upper and lower case mixed with numeric digits[/b]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 scenarioAs 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?Fri, 16 Oct 2009 09:27:35 GMTpaul.goldstrawhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspx[quote]1. The character set has already been defined in the question so regardless, nchar and nvarchar are out.[/quote]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.[quote]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.[/quote]He did say evenly distributed so I will retract my previous remark as that is pretty clear.[quote]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[/quote]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.Fri, 16 Oct 2009 09:14:58 GMTRobert Frascahttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxGreat discussion, and nice points (as always), Hugo!Fri, 16 Oct 2009 08:56:57 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxI 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!Fri, 16 Oct 2009 08:53:37 GMTMichael Poppershttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxI thought he was pretty clear on the details. There was a similar question a few months ago that simply asked which was more efficient? Char, varchar, nchar or nvarchar? That question was rightly attacked for being unclear and vague in what efficient meant, and in what context.In this case, details included the length, the distribution of that length and the language used, which is all the information needed to come to the correct answer. With regard to your points1. The character set has already been defined in the question so regardless, nchar and nvarchar are out.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.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 column4. I don't really have any problem here, I would agree with you that an integer based key would make more sense.Fri, 16 Oct 2009 08:33:39 GMTpaul.goldstrawhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxI was all smug in the fact that I realized NVarchar would be less efficient than Varchar in this instance and checked varchar. As I sipped my coffee and waited for my CORRECT response i was taken a back by the Sorry you are wrong answer. Once I read the explanation and realized my mistake, i again learned something about how to read and answer these questions and a little more on the data types.Keep these questions coming.. you always learn from your mistakesThanksSteveFri, 16 Oct 2009 07:55:49 GMTSteve Vassallohttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxThanks Hugo :-)Fri, 16 Oct 2009 07:53:34 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxWhile I like this type of question in theory, apparently, in this example "efficient" is defined only in the context of how many bytes are being stored. When asking this kind of question it should be clear what the metric is for defining what constitutes "efficient". If you don't then the choice of answers is subjective. For example:1. Using nchar or nvarchar might be more "efficient" if I'm working on an application that requires multi-language support. 2. The assumption that the actual length would be 8.5 characters wasn't stated. Suppose the average is less than 8? What about padding for lengths less than 8? Is it more efficient to have to write apps that require the use of trim functions?3. The whole point of using varchar and nvarchar is to allow flexibility. It might be argued that flexibility is more efficient.4. Is the use of a variable length "identifier" even a rational choice, let alone efficient choice, in the first place? I probably wouldn't use a variable length field as an identifier. I would use a fixed length, preferably integer, surrogate key as an identifier and store the native key (external identifier) separately. It would be less efficient from a storage point of view but more efficient from several other points of view including as a primary key and/or clustered index.We could argue endlessly about what is the most efficient datatype in a host of scenarios so, from a QOD point of view, it's important to define the scenario to avoid ambiguity.Fri, 16 Oct 2009 07:43:43 GMTRobert Frascahttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspx[quote][b]Fatal Exception Error (10/16/2009)[/b][hr]I based my answer on reality. In reality data is not immutable. What is true today probably won't be true six months down the road. If this was an interview question how would you answer?[/quote]I agree, it is hard to predict the future, and decisions made on the assumptions that the data will be distributed in the same way in the future may in the end cause bottlenecks.Fri, 16 Oct 2009 06:52:15 GMThakan.wintherhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxI based my answer on reality. In reality data is not immutable. What is true today probably won't be true six months down the road. If this was an interview question how would you answer?Fri, 16 Oct 2009 06:39:51 GMTFatal Exception Errorhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspx[quote][b]Toreador (10/16/2009)[/b][hr]The question asks which is more efficient.The explanation describes which uses least space, which is not the same thing as most efficient.I suspect that the answer is correct, but would be interested to see a full explanation as to why. How do char and varchar differ in their treatment by the optimiser, for instance?And what about storage/performance differences if the column is indexed? The question doesn't say that it is, but I'd expect that it would be.[/quote]Hi Toreador,Good questions. Let me try to answer them.In general, saving space does equate to saving performance and increasing efficiency when dealing with SQL Server. Most of the system's performance is determined by the amount of data that has to be read from and written to disk, and the amount of data that can be held in cache. There may be borderline cases where CPU cycles determine the performance of an app, but these will be the exceptions.Since you ask specifically about the optimizer - I don't think it makes much difference between these two datatypes.For indexes, the same I/O reduction considerations apply that hold for all data. Since indexes effectively duplicate some data, I/O reductions in indexed column count double. There's also another consideration when dealing with indexes, and that is fragmentation. If values added to the index are always at the "end" (as is the case with untampered-with IDENTITY columns or with NEWSEQUENTIALID() values), all index pages will neatly fill up and then be complete. If they are scattered, pages that get full will have to be split, which slows down both the insert (page split is relatively expensive) and all subsequent operations (as you now have two half-full pages, thus requiring more I/O for the same amount of data). But char or varchar does not affect this, as they both sort the same.One aspect that can reduce performance of varchar columns is how updates are treated when the new value takes more space then the old value. This is not a problem if there still are enough unused bytes on the page - but otherwise, you'll once more run into a page split, with the above disadvantages.Everything considered, I'd say that varchar has a few disadvantages that char doesn't have. Extra cpu cycles are needed to find start and end position in the row; and increased chance of page splits as rows are updated with new and longer data.I'd say that as a generic rule of thumb, one should:* use varchar if trailing spaces need to be preserved, as char can't do that;* use char if all strings are of the same length;* use char if the average string length differs up to 2 bytes from the maximum string length;* use varchar if the average string length differs more than 3 bytes from the maximum string length;* use either char or varchar, depending on actual sitation, if average length and maximum length differ more than 2 but not more than 3 bytes - but without additional info, I'd probably choose char in this case.Fri, 16 Oct 2009 06:18:08 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspx[quote][b]Hugo Kornelis (10/16/2009)[/b][hr][quote][b]Damian Widera-396333 (10/16/2009)[/b][hr]in terms of I/O we could assume fixed length data types are better than varying ones.[/quote]If, and only if,a) the average length of data is only 2 characters or less below the maximum length. I'd never dream of storing address information in fixed length columns!b) there is no need to preserve trailing spaces. If there is, varchar is the only option that won't corrupt your data.[/quote]Hugopersonally I would use varchar() data type in mentioned case but I just tried to explain why the correct answer is char(). Agree with your remarks :)Fri, 16 Oct 2009 06:15:37 GMTDamian Widera-396333http://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxthis was a good questionFri, 16 Oct 2009 05:53:31 GMTJoel Ewaldhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxThe question asks which is more efficient.The explanation describes which uses least space, which is not the same thing as most efficient.I suspect that the answer is correct, but would be interested to see a full explanation as to why. How do char and varchar differ in their treatment by the optimiser, for instance?And what about storage/performance differences if the column is indexed? The question doesn't say that it is, but I'd expect that it would be.Fri, 16 Oct 2009 05:20:27 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspx[quote][b]hakan.winther (10/16/2009)[/b][hr][quote][b]Hugo Kornelis (10/16/2009)[/b][hr]That's reallly a shame, as the only good reasons to use varchar instead of char are a difference of more than 2 between average and maximum length; or a need to preserve trailing spaces.[/quote]Of course, if i need the trailing spaces I would go for char[/quote]OOPS - misunderstanding alert!If you need to preserve trailing spaces, you MUST use varchar. Varchar stores the data with the trailing spaces as they were on input (that is, with default settings). Char pads data with spaces to the specified length and there is no way to reconstruct which spaces were already there and which were added by SQL Server to get the string at the proper length.[quote]but I have seen statements where the programmer doesn't want the trailing spaces and uses RTRIM to remove then and also uses RTRIM the in the where clause fetch the records regardless to the spaces:SELECT somecol FROM sometable WHERE RTRIM(anothercol)='somevalue'[/quote]The real problem of these programmers (apart from not understanding how SQL Server indexes work) is a lack of understanding of string comparisons. The RTRIM is completely superfluous, both for char and varchar datatypes. Trailing spaces are compared in these comparisons anyway. (Well, technically the shorter string is padded with spaces to match the length of the longer string before starting a character-by-character comparison, but that has the exact same effect as ignoring trailing spaces).Fri, 16 Oct 2009 05:13:16 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspx[quote][b]Hugo Kornelis (10/16/2009)[/b][hr]That's reallly a shame, as the only good reasons to use varchar instead of char are a difference of more than 2 between average and maximum length; or a need to preserve trailing spaces.[/quote]Of course, if i need the trailing spaces I would go for char, but I have seen statements where the programmer doesn't want the trailing spaces and uses RTRIM to remove then and also uses RTRIM the in the where clause fetch the records regardless to the spaces:SELECT somecol FROM sometable WHERE RTRIM(anothercol)='somevalue'And this causes the SQL Server to do a table or index scan (lots of IO) with terrible performance as a result. This issue is not solved completely by using varchar, but I can reduce some of the cases where the programmers are using functions on the left side of the equal sign. The correct way to solve this issue is to teach everyone to be careful of how they use functions in the where clause, and I do that all the time, but... new project, new programmers.Fri, 16 Oct 2009 05:02:06 GMThakan.wintherhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspx[quote]diamondgm (10/16/2009)diamondgm (10/16/2009)Firstly, we assume that the average is the result of an even spread (that the average length is in fact 8.5 - we only need an average of 8 for storage to be a moot point in char vs varchar).That's not an assumption, the even spread is explicitly mentioned in the question. Without it, I agree that the question would have been harder (if not impossible) to answer.I think our definition of the word 'explicit' differs dramatically. I did not find the question to indicate that the spread was even as a fact.Then our definition of the word 'explicit' does indeed differ dramatically.The question reads (direct quote; emphasis added by me):"Their length will vary from seven to ten characters, all English letters, both upper and lower case mixed with numeric digits, with an even distribution of lengths."What would it need to satisfy your definition of 'explicit'?[/quote]OMG, I didn't see that, my hublest appologies!Uncle!Fri, 16 Oct 2009 03:53:05 GMTdiamondgmhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspx[quote][b]Damian Widera-396333 (10/16/2009)[/b][hr]in terms of I/O we could assume fixed length data types are better than varying ones.[/quote]If, and only if,a) the average length of data is only 2 characters or less below the maximum length. I'd never dream of storing address information in fixed length columns!b) there is no need to preserve trailing spaces. If there is, varchar is the only option that won't corrupt your data.Fri, 16 Oct 2009 03:43:54 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspx[quote][b]diamondgm (10/16/2009)[/b][hr][quote][b]diamondgm (10/16/2009)[/b][hr]Firstly, we assume that the average is the result of an even spread (that the average length is in fact 8.5 - we only need an average of 8 for storage to be a moot point in char vs varchar).[/quote][quote]That's not an assumption, the even spread is explicitly mentioned in the question. Without it, I agree that the question would have been harder (if not impossible) to answer.[/quote]I think our definition of the word 'explicit' differs dramatically. I did not find the question to indicate that the spread was even as a fact.[/quote]Then our definition of the word 'explicit' does indeed differ dramatically.The question reads (direct quote; emphasis added by me):"Their length will vary from seven to ten characters, all English letters, both upper and lower case mixed with numeric digits, [b][i][u]with an even distribution of lengths[/u][/i][/b]."What would it need to satisfy your definition of 'explicit'?[quote][quote]Secondly, within SQL Server, the efficiency may be best to use char in that instance, but take in to account the code operations of trimming and I think it becomes debatable.[/quote][quote]I doubt it. Almost all business applications I have seen have their performance tied to an I/O bottleneck, and have the CPU twiddling it's virtual thumbs while waiting for more data to be read or written. Those few cycles the CPU spends to trim trailing spaces won't affect performance at all, it just means one less thumb to twiddle.[/quote]In the cases where this is true (which is most of the time) white space would aversely affect I/O, no? (Maybe I am very wrong here)[/quote]I/O is affected by number of bytes. Or rather, for SQL Server, I/O is affected by the number of rows per 8K page. If the row length is such that saving a few bytes per row increases the unused space on each page without increasing the number of rows (e.q. when row length goes from 2200 to 2100, you still get only 4 rows per page), I/O will not be affected. When the number of rows increases, less I/O will be required to do the same amount of work.Whether bytes contain whitespace or character data is not relevant at all. Just the number of bytes - and most of all the integer part of the outcome of dividing 8,060 by that number.[quote]I agree with you on a purist level, but practically, I would go with varchar still simply for the programmers' ease.[/quote][quote][quote]How, exactly, is varchar easier for a programmer?[/quote]Have you never had a programmer complain about having to trim on certain fields and not on others and wishing for a "standard" approach to text data?[/quote]:-D Programmers will complain about everything. Even about the requirement to test.If a programmer is too lazy to type RTRIM(...) around a column name when coding a report and is prepared to let user performance suffer to satisfy his laziness, I'm done with him or her very quickly.(And if push comes to shove, I can always create a view to cast the column to varchar and trim the trailing spaces)Fri, 16 Oct 2009 03:42:00 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspx[quote][b]cengland0 (10/16/2009)[/b][hr]This one got me again. Another trick question.The question asked, "Which of these data types will be more efficient?"Had it asked, "Which of these data types take less hard drive space?" I might have chosen the correct answer. I selected Varchar because it is more efficient when programming because char pads with extra spaces at the end and you need to rtrim the column before you produce your final output.[/quote]But in terms of I/O we could assume fixed length data types are better than varying ones.Fri, 16 Oct 2009 03:21:06 GMTDamian Widera-396333http://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspx[quote][b]diamondgm (10/16/2009)[/b][hr]Firstly, we assume that the average is the result of an even spread (that the average length is in fact 8.5 - we only need an average of 8 for storage to be a moot point in char vs varchar).[/quote][quote]That's not an assumption, the even spread is explicitly mentioned in the question. Without it, I agree that the question would have been harder (if not impossible) to answer.[/quote]I think our definition of the word 'explicit' differs dramatically. I did not find the question to indicate that the spread was even as a fact.[quote]Secondly, within SQL Server, the efficiency may be best to use char in that instance, but take in to account the code operations of trimming and I think it becomes debatable.[/quote][quote]I doubt it. Almost all business applications I have seen have their performance tied to an I/O bottleneck, and have the CPU twiddling it's virtual thumbs while waiting for more data to be read or written. Those few cycles the CPU spends to trim trailing spaces won't affect performance at all, it just means one less thumb to twiddle.[/quote]In the cases where this is true (which is most of the time) white space would aversely affect I/O, no? (Maybe I am very wrong here)[quote]I agree with you on a purist level, but practically, I would go with varchar still simply for the programmers' ease.[/quote][quote]How, exactly, is varchar easier for a programmer?[/quote]Have you never had a programmer complain about having to trim on certain fields and not on others and wishing for a "standard" approach to text data?I think you have raised excellent objections to my way of thinking about this problem. And on paper (assuming the average is 8.5), I can not disagree. Though in reality, I would still use varchar(10), if only for the convenience to the programmers at a realatively low performace cost.Fri, 16 Oct 2009 03:20:36 GMTdiamondgmhttp://www.sqlservercentral.com/Forums/Topic803943-1658-1.aspxThis one got me again. Another trick question.The question asked, "Which of these data types will be more efficient?"Had it asked, "Which of these data types take less hard drive space?" I might have chosen the correct answer. I selected Varchar because it is more efficient when programming because char pads with extra spaces at the end and you need to rtrim the column before you produce your final output.Fri, 16 Oct 2009 02:56:03 GMTcengland0