InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 06:38:34 GMT20http://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxNice attempt. Here in this article you should explain why SQL server does like this. I'm referring to the Index Statistitics. You can also mention in the article that if all of the columns are used as predicate, order doesn't matter more precisly it is about the leading column of the index matters not the order.Sat, 11 May 2013 07:07:25 GMTsandippanihttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxI agree with the "spend a little more time with the article" focus.When i first read the topic, i got interested, and it was an interesting read, but i do think it also was a little bit shallow and with some lack of research, even more if you consider you're talking about, i think, one of the more complex topics on database development.It's just constructive criticism.Regards DGSat, 27 Nov 2010 22:36:56 GMTdg1407http://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxI too would like to thank you for the article. I recommend you follow the advice by Grant that I quoted.[quote][b]Grant Fritchey (11/23/2010)[/b][hr]Thanks for the article Sarvesh. You may want to spend a little more time on the next one, just nail down why these things occur. As you showed, it's not simply the order in which columns are stored, rather, it's which column is on the front, the leading edge, of the index. Answer the question, why does the leading edge matter, and you'll have a much improved article.[/quote]Wed, 24 Nov 2010 10:09:05 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspx[quote][b]gosh (11/24/2010)[/b][hr]I expected proof that there was a performance difference between the order of columns in a composite index or a performance difference in the order of columns in the where-clause.[/quote]You won't find that because the order of columns in a where clause is utterly irrelevant. It makes no difference what order you specify the where clause predicates.The order of columns in an index is very important, not so much for a single query, but for when you're trying to get as few indexes as possible to support as many queries as possible.Several links to my blog posts on this have already been posted here, so I won't post again. Go and read over them.[quote]I still think that the title is misleading. Maybe something along the lines: 'Primary Column in a Composite Index is the most Important'.[/quote]It's not. The order of columns (all of them) is important. Maybe the leading column is the most important, but the order of the rest of the columns in the index is important too.Wed, 24 Nov 2010 09:14:03 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxThe title of the article is, 'Column Order in an Index'. I expected proof that there was a performance difference between the order of columns in a composite index or a performance difference in the order of columns in the where-clause.Instead, the author tells us that the same execution plan is used when the order in the where-clause is reversed and doesn't touch upon the order within the composite index.However, he does tell us, that the number of parameters in the where-clause does make a difference when we have a composite clause. If the first column is only included, then the same execution plan is used, but not with the second.I still think that the title is misleading. Maybe something along the lines: 'Primary Column in a Composite Index is the most Important'.Wed, 24 Nov 2010 07:59:45 GMTSean Redmondhttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspx[quote][b]gosh (11/24/2010)[/b][hr]I have to admit that I am not impressed with the title. It is wrong. According to your article, the column order in a composite index is not important.[/quote]Go back over the article and comments. The order in the index is important and takes some knowledge on which columns to include in each index.With an index on (Lastname, Firstname), as long as you are searching on Lastname and Firstname in your WHERE clause, the optimizer is smart enough to figure out the right order to search with to use the (Lastname, Firstname) index. However, only searching on Firstname will cause the optimizer to choose either a table/index scan or use a different index.Visualize the index as this:Lastname, FirstnameAng, JoeAngelou, MitchHenry, BenSelby, JoshZenith, SharpIf you are searching for only Firstname, how would this index be helpful? You would have to scan the whole index. And if you have to scan the whole index, you might as well scan the table. But if you search on only LastName, the index is still useful, even though you're not looking for Firstname. And it's even better if you search for Lastname but want to return Firstname too because it's part of the index so there isn't a bookmark lookup on the table.One of the keys in designing an index is to know how the data will be searched. If you always search on Lastname and sometimes Lastname + Firstname, then the index (Lastname, Firstname) will serve you well. If you sometimes need to search on Firstname but still plan on the previous statement being true, then create an index on (Firstname) INCLUDE (Lastname).Wed, 24 Nov 2010 07:37:52 GMTGatekeeperhttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxGail,Excellent point. I should have mentioned that a missing propert for the first column of a composite index results in an index scan. Glad you did.The technique doesn't work as well when the missing column is the first item in the key. It will indeed do a complete index scan.The technique works better the deeper into the composite index the missing column value is found.Even with a complete index scan, given that no better index exist, this may be more desireable than a table scan.As a note, if the scenario discussed was a reality, I would probably create two indexes. One composite index without the last name, and an index on Last Name only. Then SQL Server can return the intersection of the two indexes before going to the base data if Last name is required. If not, it will simply use the appropriate index.BenWed, 24 Nov 2010 07:05:46 GMTtaylor_benjaminhttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspx[quote][b]gosh (11/24/2010)[/b][hr]I have to admit that I am not impressed with the title. It is wrong...Column order is irrelevant. Column presence is. [/quote]Lol, and here I was criticizing the article because I didn't believe anyone actually believed this silliness. If you ever paid for a computer science course, I suggest you track down the professor and demand a full refund.Wed, 24 Nov 2010 02:22:24 GMTMichael Ebayahttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxI have to admit that I am not impressed with the title. It is wrong. According to your article, the column order in a composite index is not important.From the article you state, "Let's now reverse the order of the WHERE clause in the select statement. If you now run the select statement shown below you will still see the same execution plan as shown in Fig 1."This article states that composite indexes work better when all columns in the composite index have an explicit value in the WHERE-clause. The composite index is less efficient when one or more of the columns in the index are omitted. Column order is irrelevant. Column presence is.Wed, 24 Nov 2010 01:39:13 GMTSean Redmondhttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspx[quote][b]taylor_benjamin (11/23/2010)[/b][hr]In the case of your article, the optimizer was intelligent enough to know that the existing index was more optimized than a table scan. When this doesn't work, simply specify not restricting criteria in place of missing attributes.With your example, included LastName > 'A' in your where clause will meet the qualifications for the index.[/quote]Maybe, but go and look at what's actually happened in that case.With the leading column searched on an inequality, SQL can't seek on both columns, only on the first. So it seeks on the first, and finds all the rows. Great, you've got yourself an index seek that is in essence an index scan (read of all the pages in an index). Looks good in the execution plan if you don't know what you're looking for, but it's no more efficient than the query would have been had you omitted that predicate.[url]http://sqlinthewild.co.za/index.php/2009/03/05/when-is-a-seek-actually-a-scan/[/url]If SQL went for a table/clustered index scan over the index scan, it means the index is not covering and it thinks that the key lookups will be more expensive than a clustered index scan. A non-filtering predicate is unlikely to change its cost estimates. From the statistics it will be able to tell that LastName > 'A' has a selectivity of 1 (all rows qualify)Tue, 23 Nov 2010 22:58:21 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspx[quote][b]cengland0 (11/23/2010)[/b][hr]Doh, I just discovered the maximum key length is 900 bytes so combining two VARCHAR(500) columns into one index results in a warning:The maximum key length is 900 bytes. The ***indexname*** has maximum length of 1000 bytes. For some combinations of large values, the insert/update operation will fail.[/quote]If you can't control the SSIS package from the vendor but you control the data afterwards, you could create two computed columns then create your indexes on the computed columns. http://msdn.microsoft.com/en-us/library/ms189292.aspxTue, 23 Nov 2010 22:22:55 GMTGatekeeperhttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspx[quote][b]taylor_benjamin (11/23/2010)[/b][hr]One way to get SQL Server to consider an index it would otherwise exclude in preference to a table scan is to provide criteria for all columns of the index.In the case of your article, the optimizer was intelligent enough to know that the existing index was more optimized than a table scan. When this doesn't work, simply specify not restricting criteria in place of missing attributes.With your example, included LastName > 'A' in your where clause will meet the qualifications for the index.It's an old school trick, but it still works.[/quote]Aiy yai yai...no one should ever do this, for a couple of different reasons. If the QO isn't choosing your index, it usually has a very good reason why not. In the example you gave, for instance, you're still going to wind up scanning all the rows, index or no. If the QO is choosing a table scan over an index scan, the table scan is probably going to be faster.Furthermore, in the rare case where the QO isn't selecting the optimal index, you don't want to use a "trick" like this. You just use a WITH index hint to instruct the QO which index it should be using.Tue, 23 Nov 2010 20:11:43 GMTMichael Ebayahttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxWell done.One way to get SQL Server to consider an index it would otherwise exclude in preference to a table scan is to provide criteria for all columns of the index.In the case of your article, the optimizer was intelligent enough to know that the existing index was more optimized than a table scan. When this doesn't work, simply specify not restricting criteria in place of missing attributes.With your example, included LastName > 'A' in your where clause will meet the qualifications for the index.It's an old school trick, but it still works.The neat part is that you can do this in any missing column of a composite indexWrite more...Cheers,BenTue, 23 Nov 2010 18:14:53 GMTtaylor_benjaminhttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspx[quote][b]David Walker-278941 (11/23/2010)[/b][hr]If you change the columns to Char(8) now, there won't ever be 450 characters in the column! If the vendor sending the data says that the data has 8 digits, you can go with that. You can always update it to [Var]char(12) or [Var]char(20) or whatever, in the future, if the vendor starts sending a larger field. Or set them to Varchar(20) now and then index the two columns, and say that you're planning for future growth of the field.[/quote]We thought of that but we have no control over the SSIS package that the vendor uses. I think it is set to fail on any warnings. As you know, if you try to insert a VARCHAR(500) from the source table into a CHAR(8), you get a truncation possible warning. That causes the package to fail and we receive no data. As GilaMonster stated, I'm certain there are a maximum of 8 digits in each of the two columns so I should be okay.Tue, 23 Nov 2010 11:32:47 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspx[quote][b]GilaMonster (11/23/2010)[/b][hr][quote][b]cengland0 (11/23/2010)[/b][hr]Doh, I just discovered the maximum key length is 900 bytes so combining two VARCHAR(500) columns into one index results in a warning:The maximum key length is 900 bytes. The ***indexname*** has maximum length of 1000 bytes. For some combinations of large values, the insert/update operation will fail.[/quote]If you *know* there's only ever 8 characters in each column, you can ignore that warning. However if one day there are over 450 characters in each.....[/quote]If you change the columns to Char(8) now, there won't ever be 450 characters in the column! If the vendor sending the data says that the data has 8 digits, you can go with that. You can always update it to [Var]char(12) or [Var]char(20) or whatever, in the future, if the vendor starts sending a larger field. Or set them to Varchar(20) now and then index the two columns, and say that you're planning for future growth of the field.Tue, 23 Nov 2010 11:13:07 GMTDavid Walker-278941http://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspx[quote][b]cengland0 (11/23/2010)[/b][hr]Doh, I just discovered the maximum key length is 900 bytes so combining two VARCHAR(500) columns into one index results in a warning:The maximum key length is 900 bytes. The ***indexname*** has maximum length of 1000 bytes. For some combinations of large values, the insert/update operation will fail.[/quote]If you *know* there's only ever 8 characters in each column, you can ignore that warning. However if one day there are over 450 characters in each.....Tue, 23 Nov 2010 11:06:54 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspx[quote][b]cengland0 (11/23/2010)[/b][hr]Doh, I just discovered the maximum key length is 900 bytes so combining two VARCHAR(500) columns into one index results in a warning:The maximum key length is 900 bytes. The ***indexname*** has maximum length of 1000 bytes. For some combinations of large values, the insert/update operation will fail.[/quote]Surely they will allow you to redefine the columns as Char(8) or Varchar(8), won't they? You might get a warning that some data MIGHT be truncated, but if the data is all 8 characters or less, you can ignore this warning. THEN you can index the columns.Tue, 23 Nov 2010 11:04:59 GMTDavid Walker-278941http://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxDoh, I just discovered the maximum key length is 900 bytes so combining two VARCHAR(500) columns into one index results in a warning:The maximum key length is 900 bytes. The ***indexname*** has maximum length of 1000 bytes. For some combinations of large values, the insert/update operation will fail.Tue, 23 Nov 2010 10:52:17 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspx[quote][b]GilaMonster (11/23/2010)[/b][hr][quote][b]cengland0 (11/23/2010)[/b][hr][quote][b]Wilfred van Dijk (11/23/2010)[/b][hr]aha, the never-ending question about multicolumn or single column indexes!Again, check Gail's excellent articles about indexes:[url]http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/[/url][/quote]Thanks for the link but I get "404 - File or directory not found."[/quote]Wilfred messed up the forum coding (look at the url, there's sqlservercentral at the front probably due to the new line character.)[url]http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/[/url][/quote]Great Article. I bookmarked that one as a keeper.Tue, 23 Nov 2010 10:31:06 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspx[quote][b]Michael Ebaya (11/23/2010)[/b][hr][quote][b]cengland0 (11/23/2010)[/b][hr]Please don't laugh at me for asking this question.Why would you create a compound index with both last name and first name together? Why not two separate indexes where one is for last name and the other is for first name. That should handle any combination that you have in your where clause.[/quote]Because indexes don't work the way you think they do. Indexes sort off their key expression. If that expression contains two columns, the table is sorted by both. If it contains one column only, then its only sorted by that single column, period.Without getting into too much technical detail, SQL contains an optimization called index intersection that allows two indexes to act a wee bit like one compound index. But "a wee bit" is the operative term here; the performance is not the same.To use your firstname, lastname example. If you have a single "John Smith" in your table, a compound index will walk the tree right to that record-- all done! No scanning required. Two indexes, one on each column, will only allow the engine to find all Smiths, then find all Johns, then laboriously intersect the result result to find which ones match both criteria.Performance takes a further hit when you want to order by multiple columns, as a single index will return the rows in proper order, whereas results from intersected indexes must be manually sorted each time.[/quote]Thanks, that was an excellent explanation. I can already see some tables where I can improve the indexes with this strategy. I have a frequently used 12 million row table that has a 16 digit number separated into two columns with 8 digits each. The developers always put both columns in their where clause and would never select on just one of them. I'm going to implement this improvement right away.Oh, please don't ask about why the two columns are separate. I would never have designed it this way but it comes from an application that pushes the data to us each day and their SSIS package fails if we modify any of the columns. I'm even embarrassed to say that these both are VARCHAR(500) columns even knowing that a maximum of 8 numeric characters will fit. They even drop leading zeros when pushing the data to us.Tue, 23 Nov 2010 10:24:43 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspx[quote][b]Franky Leeuwerck (11/23/2010)[/b][hr]Previous link can be found here :http://sqlserverpedia.com/wiki/Index_Selectivity_and_Column_Order[/quote]The wiki article was adapted (with my permission) from my two blog posts on index column order, the one on equality that Wilfred posted and one on inequalitiesTue, 23 Nov 2010 09:52:24 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspx[quote][b]cengland0 (11/23/2010)[/b][hr][quote][b]Wilfred van Dijk (11/23/2010)[/b][hr]aha, the never-ending question about multicolumn or single column indexes!Again, check Gail's excellent articles about indexes:[url]http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/[/url][/quote]Thanks for the link but I get "404 - File or directory not found."[/quote]Wilfred messed up the forum coding (look at the url, there's sqlservercentral at the front probably due to the new line character.)[url]http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/[/url]Tue, 23 Nov 2010 09:50:56 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspx"This does not mean that the index 'NonClust_Contact_Name' was not useful. It's still much better than the Clustered scan."*WHAT* is still much better [u]than[/u] the clustered scan? I'm confused by that last sentence.Tue, 23 Nov 2010 09:38:36 GMTDavid Walker-278941http://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxseems SSC adds something in front of it, copy/paste: http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/Tue, 23 Nov 2010 09:22:06 GMTWilfred van Dijkhttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspx[quote][b]cengland0 (11/23/2010)[/b][hr]Please don't laugh at me for asking this question.Why would you create a compound index with both last name and first name together? Why not two separate indexes where one is for last name and the other is for first name. That should handle any combination that you have in your where clause.[/quote]Because indexes don't work the way you think they do. Indexes sort off their key expression. If that expression contains two columns, the table is sorted by both. If it contains one column only, then its only sorted by that single column, period.Without getting into too much technical detail, SQL contains an optimization called index intersection that allows two indexes to act a wee bit like one compound index. But "a wee bit" is the operative term here; the performance is not the same.To use your firstname, lastname example. If you have a single "John Smith" in your table, a compound index will walk the tree right to that record-- all done! No scanning required. Two indexes, one on each column, will only allow the engine to find all Smiths, then find all Johns, then laboriously intersect the result result to find which ones match both criteria.Performance takes a further hit when you want to order by multiple columns, as a single index will return the rows in proper order, whereas results from intersected indexes must be manually sorted each time.Tue, 23 Nov 2010 08:11:03 GMTMichael Ebayahttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspx[quote][b]Wilfred van Dijk (11/23/2010)[/b][hr]aha, the never-ending question about multicolumn or single column indexes!Again, check Gail's excellent articles about indexes:[url]http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/[/url][/quote]Thanks for the link but I get "404 - File or directory not found."Tue, 23 Nov 2010 07:38:27 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxaha, the never-ending question about multicolumn or single column indexes!Again, check Gail's excellent articles about indexes:[url]http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/[/url]Tue, 23 Nov 2010 06:55:06 GMTWilfred van Dijkhttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxPlease don't laugh at me for asking this question.Why would you create a compound index with both last name and first name together? Why not two separate indexes where one is for last name and the other is for first name. That should handle any combination that you have in your where clause.Tue, 23 Nov 2010 06:44:51 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxSarvesh, This was a good article, with the potential to be much better if you were to expand on it. You leave out a lot of why things work this way leaving it at a very entry level atricle.I think it great that you did the article, but please - put more effort into it. Why does the index work this way? How do statistics impact it? What is the difference between an index scan and an index seek? etc...Tue, 23 Nov 2010 06:16:22 GMTsjimmohttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxThanks for the feedback Grant. Much appreciated. Will keep that in mind for my next article.Tue, 23 Nov 2010 06:12:24 GMTss-457805http://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxThe article may be short and short on detail, however, the really good thing was that it lead me to this discussion and the links here. Thank you!:-)Tue, 23 Nov 2010 05:23:41 GMTUkonhttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxGood Article to understand Non clustered index usage.Tue, 23 Nov 2010 04:47:13 GMTvishalkumar_dobariyahttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxThanks for the article Sarvesh. You may want to spend a little more time on the next one, just nail down why these things occur. As you showed, it's not simply the order in which columns are stored, rather, it's which column is on the front, the leading edge, of the index. Answer the question, why does the leading edge matter, and you'll have a much improved article.Tue, 23 Nov 2010 04:23:43 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxKiran, B-trees really aren't that complex to understand. In all four of your examples, there will be an index seek. In case 1 and 4, the index will then be range-scanned for all ABC values (in case #1, this is optimal anyway). In case 2 and 3, the second WHERE predicate is also an index key, so the seek will proceed directly to the first matching value from the result set.Tue, 23 Nov 2010 04:05:17 GMTMichael Ebayahttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxFurther to your scenario, Can you help me with your feedback and comments on the below scenarioConsidering the same scenario as yoursScenario 1: I have one more index lastname , emailid then when i fire a query then how will be the execution plan be for following WHERE clauses1. WHERE LastName='ABC'2. WHERE LastName='ABC' AND EMAILid ='abc@xyz.com'3. WHERE LastName='ABC' AND EMAILid LIKE 'abc@%'4. WHERE LastName='ABC' AND firstName ='PQR'Thanks in advanceRegards,Kiran R. KhotTue, 23 Nov 2010 03:18:41 GMTKiran Khothttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxSorry, you shouldn't be writing SQL if you think this isn't patently obvious.Tue, 23 Nov 2010 02:44:25 GMTpaul s-306273http://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxAn article so absolutely shallow and superficial as to be entirely braindead. I won't go into why (if you don't already know, then you shouldn't be writing SQL code in the first place) but I will correct one error. An index scan is not "much better" than a table scan. It's exactly the same cost as scanning a table as wide as that index. In this example, if the table had no other rows than last name and first name, the cost would be identical whether table or index scanning, and the QO would only favor the index scan on the assumption that the index is more likely to already be cached.Tue, 23 Nov 2010 01:50:27 GMTMichael Ebayahttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxPrevious link can be found here :http://sqlserverpedia.com/wiki/Index_Selectivity_and_Column_OrderTue, 23 Nov 2010 01:17:16 GMTFranky Leeuwerckhttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxPoor summary about column considerations in an index. For the complete picture, I suggest [url]http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/[/url]Tue, 23 Nov 2010 00:52:06 GMTWilfred van Dijkhttp://www.sqlservercentral.com/Forums/Topic1024819-2810-1.aspxNice post :-)Tue, 23 Nov 2010 00:49:30 GMTHardy21