InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 00:56:06 GMT20http://www.sqlservercentral.com/Forums/Topic705517-149-1.aspxOops. I misread what they had created. Sorry. I saw it as a compound index. My bad.Tue, 28 Apr 2009 07:23:37 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic705517-149-1.aspx[quote][b]Grant Fritchey (4/28/2009)[/b][hr][quote]SELECT * FROM TEST101 WHERE CODE=105 AND ACNO=14000ORDER BY CODE--WHY IT IS GOING FOR CLUSTRED INDEX OF COLUMN "ID"--Now it is going for non clustered index, this is normal[/quote]This is an example of a covering index, so it never even goes to the cluster to return since all the columns are included in the nonclustered index (including the cluster key column, ID).[/quote]Actually not. There's no index (other than the cluster) that's covering. I would guess that this query returns relatively few rows and the optimiser's picking one of the nonclustered indexes (probably the one on code, since it will satisfy the order by as well as the seek), seeking on that and then doing a bookmark/key lookup to fetch the other column.Tue, 28 Apr 2009 07:21:21 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic705517-149-1.aspxhi Grant,Thanks for ur reply and i will be surly reading those articles and also will pickup the book Kalen Delaney's Inside SQL Server 2005.....thanks for ur time and knowledge u shared....MithunTue, 28 Apr 2009 06:28:01 GMTmithun.gitehttp://www.sqlservercentral.com/Forums/Topic705517-149-1.aspxYou should read Gail's articles first. They'll answer a lot of your questions. You might want to pick up Kalen Delaney's book Inside SQL Server 2005: The Storage Engine to get some idea of how things work within SQL Server.[quote][b]mithun.gite (4/28/2009)[/b][hr]After adding 200000 rows to same table the out put has changed in my most questions , the observation is asper belowSELECT * FROM TEST101--WHY IT IS GOING FOR CLUSTERED INDEX OF COLUMN "ID"?--same in second test[/quote]For the same reason as before. The clustered index defines not only search criteria for an index, but the storage of the data itself. So when you have a table with a clustered index, the cluster is always used for data retrieval (except in the case of covering indexes, etc.)[quote]SELECT * FROM TEST101 WHERE ID=5 ORDER BY CODE--WHY IT IS USING NON CLUSTERED INDEX OF COLUMN "CODE" ?--Now it has changed to Clustred index seek, this is normal.[/quote]There's enough data in the index for it to be selective enough to find what it needs by pulling out a particular value. By the way, since I assume this only will ever return a single row, the ORDER BY clause is pretty redundant.[quote]SELECT CODE FROM TEST101 WHERE ID=5 AND CODE=105 AND ACNO=14000ORDER BY CODE--WHY IT IS GOING ONLY FOR CLUSTRED INDEX OF COLUMN "ID" when it is having 3 three diffrent index on three diff columns.--same in second test[/quote]Because you're using the ID value of the clustered index and the ID is unique, the rest of the values won't really matter. The optimizer is pretty smart.[quote]SELECT * FROM TEST101 WHERE CODE=105 AND ACNO=14000ORDER BY CODE--WHY IT IS GOING FOR CLUSTRED INDEX OF COLUMN "ID"--Now it is going for non clustered index, this is normal[/quote]This is an example of a covering index, so it never even goes to the cluster to return since all the columns are included in the nonclustered index (including the cluster key column, ID).[quote]SELECT * FROM TEST101 WHERE ID=5 ORDER BY ID,CODE--WHY IT GOING FOR NON CLUSTRED INDEX OF COLUMN "CODE"--Now this is going for clustered index, this is notrmalSELECT CODE FROM TEST101 WHERE CODE=105 ORDER BY ID,CODE--WHY IT GOING FOR NON CLUSTRED INDEX OF COLUMN "CODE"-- same it is going for non clustered index[/quote]Yep, all these are correct.Definitely read through Gail's articles. I'd also strongly suggest you pick up the book recommend above. There are other books out there that will help as well.Tue, 28 Apr 2009 06:02:33 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic705517-149-1.aspx[quote]Try adding a lot more data (at least 10000 rows) and try again. I'm guessing that you'll see quite a different behaviour on some of them. 10 rows is far to little to do meaningful analysis of indexing.As for links, try these:[url]http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/[/url][url]http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/[/url][url]http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/[/url][/quote]After adding 200000 rows to same table the out put has changed in my most questions , the observation is asper belowSELECT * FROM TEST101--WHY IT IS GOING FOR CLUSTERED INDEX OF COLUMN "ID"?--same in second testSELECT * FROM TEST101 WHERE ID=5 ORDER BY CODE--WHY IT IS USING NON CLUSTERED INDEX OF COLUMN "CODE" ?--Now it has changed to Clustred index seek, this is normal.SELECT CODE FROM TEST101 WHERE ID=5 AND CODE=105 AND ACNO=14000ORDER BY CODE--WHY IT IS GOING ONLY FOR CLUSTRED INDEX OF COLUMN "ID" when it is having 3 three diffrent index on three diff columns.--same in second testSELECT * FROM TEST101 WHERE CODE=105 AND ACNO=14000ORDER BY CODE--WHY IT IS GOING FOR CLUSTRED INDEX OF COLUMN "ID"--Now it is going for non clustered index, this is normalSELECT * FROM TEST101 WHERE ID=5 ORDER BY ID,CODE--WHY IT GOING FOR NON CLUSTRED INDEX OF COLUMN "CODE"--Now this is going for clustered index, this is notrmalSELECT CODE FROM TEST101 WHERE CODE=105 ORDER BY ID,CODE--WHY IT GOING FOR NON CLUSTRED INDEX OF COLUMN "CODE"-- same it is going for non clustered indexbut still in some cases i m confuse abt optimizer selecting which index...but now i will go thru this links [url]http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/[/url][url]http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/[/url][url]http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan and then again will come with new doubts...thanks gaillllll, thank u very much, have a greta dayMithunTue, 28 Apr 2009 04:29:01 GMTmithun.gitehttp://www.sqlservercentral.com/Forums/Topic705517-149-1.aspxI was just expecting reply from u as i just saw u replyed some other post answers.....thanks for repl Gail ....fine i will put more data into it and will test again and will post again , and thanks for giving explaination for each one....thas really kind of uMithunTue, 28 Apr 2009 03:15:55 GMTmithun.gitehttp://www.sqlservercentral.com/Forums/Topic705517-149-1.aspx[quote][b]mithun.gite (4/27/2009)[/b][hr]SELECT * FROM TEST101--WHY IT IS GOING FOR CLUSTERED INDEX OF COLUMN "ID"?[/quote]Because there's no filter of any form. That query reads the entire table, hence the only way to do it is to scan the clustered index[quote]SELECT CODE FROM TEST101 WHERE ID=5 ORDER BY CODE--WHY IT IS USING NON CLUSTERED INDEX OF COLUMN "CODE" ?[/quote]Probably because the optimiser thinks that it's faster to scan the index on code and discard rows that don't match the ID than to seek on the cluster and then have to sort the resulting row set. All the nonclustered indexes have the clustering key included.[quote]SELECT CODE FROM TEST101 WHERE ID=5 AND CODE=105 AND ACNO=14000ORDER BY CODE--WHY IT IS GOING ONLY FOR CLUSTRED INDEX OF COLUMN "ID"[/quote]Because SQL generally only uses one index per table when producing a query plan and since the only index that has ID, Code and AcNo is the clustered index, it will seek on that one[quote]SELECT CODE FROM TEST101 WHERE CODE=105 AND ACNO=14000ORDER BY CODE--WHY IT IS GOING FOR CLUSTRED INDEX OF COLUMN "ID"[/quote]Probably because it thinks that the two nonclustered indexes aren't selective enough and that it's cheaper to scan the cluster than to seek on either index and have to do bookmark/key lookups[quote]SELECT CODE FROM TEST101 WHERE ID=5 ORDER BY ID,CODE--WHY IT GOING FOR NON CLUSTRED INDEX OF COLUMN "CODE"[/quote]It shouldn't be. That one should seek on the cluster. Possibly because the table is so small the scan of the noncluster is slightly faster[quote]SELECT CODE FROM TEST101 WHERE CODE=105 ORDER BY ID,CODE--WHY IT GOING FOR NON CLUSTRED INDEX OF COLUMN "CODE"[/quote]Because you're filtering on CodeTry adding a lot more data (at least 10000 rows) and try again. I'm guessing that you'll see quite a different behaviour on some of them. 10 rows is far to little to do meaningful analysis of indexing.As for links, try these:[url]http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/[/url][url]http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/[/url][url]http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/[/url]Tue, 28 Apr 2009 02:58:18 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic705517-149-1.aspxDear Friends,first of all Hello to you all .....I have some confusions regrarding the Optimizer behaviour, so wanted to put some questions to you all so i can get some good answers on that and can clear things in my mind.Is there any particular rule about how optimizer selects the indexes? if is there then please let me know it , give me any good link so reading that i can make my doubts clear.I did some testing results were confusing for me, so i m pasting it here please look ito it..create table test101 (id int identity(1,1),code int ,Acno int)insert into test101select 101,10000 union allselect 102,11000 union allselect 103,12000 union allselect 104,13000 union allselect 105,14000 union allselect 106,15000 union allselect 107,16000 union allselect 108,17000 union allselect 109,18000 union allselect 110,19000CREATE CLUSTERED INDEX C1 ON TEST101(ID)CREATE NONCLUSTERED INDEX C2 ON TEST101(CODE)CREATE NONCLUSTERED INDEX C3 ON TEST101(ACNO)----------------------------------------------------------------------------------Now the Testing--there are total 3 indexes on tableSELECT * FROM TEST101--WHY IT IS GOING FOR CLUSTERED INDEX OF COLUMN "ID"?SELECT CODE FROM TEST101 WHERE ID=5 ORDER BY CODE--WHY IT IS USING NON CLUSTERED INDEX OF COLUMN "CODE" ?SELECT CODE FROM TEST101 WHERE ID=5 AND CODE=105 AND ACNO=14000ORDER BY CODE--WHY IT IS GOING ONLY FOR CLUSTRED INDEX OF COLUMN "ID"SELECT CODE FROM TEST101 WHERE CODE=105 AND ACNO=14000ORDER BY CODE--WHY IT IS GOING FOR CLUSTRED INDEX OF COLUMN "ID"SELECT CODE FROM TEST101 WHERE ID=5 ORDER BY ID,CODE--WHY IT GOING FOR NON CLUSTRED INDEX OF COLUMN "CODE"SELECT CODE FROM TEST101 WHERE CODE=105 ORDER BY ID,CODE--WHY IT GOING FOR NON CLUSTRED INDEX OF COLUMN "CODE"I will repeat my confusion , can u tell me by seeing the query how can we say which index would be used?Thanks in Advance......Any guidence to me would be highly appreciated..Thanks & Regards,Mithun GiteMon, 27 Apr 2009 23:54:24 GMTmithun.gite