Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

DDL

create table t
(
    id int,
    id1 int
)

create index Example_Index
  On t(id,id1)

create index Example1_Index
  On t(id1,id)

DML

insert into t(id, id1)values(1, 100)
insert into t(id, id1)values(1, 101)
insert into t(id, id1)values(2, 103)
insert into t(id, id1)values(1, 104)
insert into t(id, id1)values(3, 105)
insert into t(id, id1)values(1, 106)
insert into t(id, id1)values(2, 107)
insert into t(id, id1)values(3, 108)

Sql Query - select * from t Where id = 107 -- uses Example_Index

Sql Query - select * from t Where id1 = 107 -- uses Example1_Index

Sql Query - select * from t -- uses Example1_Index

Confusion - How does the system decides to use Example1_Index

Below are the details...

enter image description here

share|improve this question

migrated from stackoverflow.com Jul 19 '12 at 17:58

This question came from our site for professional and enthusiast programmers.

1  
SQL Server determines the index to use largely from statistics, and you do not have enough data in these tables for it to matter. SQL Server knows this ... –  RBarryYoung Jul 22 '12 at 19:08

4 Answers 4

up vote 0 down vote accepted

The index created latest is used or index with higher index id is being used.This is shown in below query but this is just the observation based on below query and might not be consistent behaviour as i couldnt find any documentaion from MS related to this. Run below query and then run the same by swapping the two index creation statements.

But as long as optimizer picks the best index it shouldnt bother you.In this case both indexes are same.

drop table t
go
create table t 
( 
    id int, 
    id1 int 
) 


create index Example_Index 
  On t(id,id1) 

 create index Example1_Index 
  On t(id1,id) 
 go
 insert into t(id, id1)values(1, 100) 
insert into t(id, id1)values(1, 101) 
insert into t(id, id1)values(2, 103) 
insert into t(id, id1)values(1, 104) 
insert into t(id, id1)values(3, 105) 
insert into t(id, id1)values(1, 106) 
insert into t(id, id1)values(2, 107) 
insert into t(id, id1)values(3, 108) 
go
select * from sys.dm_db_index_physical_stats(9,object_id('t'),null,null,'DETAILED')
go
dbcc freeproccache
go
select * from t Where id = 107 -- uses Example_Index

select * from t Where id1 = 107 -- uses Example1_Index

select * from t -- uses Example1_Index
go
dbcc freeproccache
go
select * from t Where id = 107 -- uses Example_Index

select * from t -- uses still Example1_Index even though that index is not used at all.However, this index id is 3 higher than example_index.
go
--now try the same thing but change the order of the index and you will see example_index is used.
share|improve this answer
    
You might observe that behavior immediately, but what about over time? I'd be very careful about making your observation sound factual or guaranteed. –  Aaron Bertrand Jul 21 '12 at 14:18
    
I am not saying that this is consistent behaviour because i have tried just one or two scenarios.Also, I do not know someone in Microsof to confirm this behaviour.Also till now I have not seen any documentation,there might exists but i havent see it yet.I have mentioned in the end that as long as the optimizer is picking correct index we should not bother how it selects between two indexes which are same as is in this case. –  Gulli Meel Jul 21 '12 at 17:24
1  
Your first sentence sounds like you are stating a fact, when in truth that isn't a fact at all, unless you have some reference you can point to. Please don't state singular observations as facts; these lead to incorrect assumptions and myths. –  Aaron Bertrand Jul 21 '12 at 17:40
    
Changed the sentense.Hoepfully, it doesnt sound fact now. –  Gulli Meel Jul 21 '12 at 17:45
    
Need some time to look into it i will revert very soon –  RGI Jul 25 '12 at 4:37

First two queries are self-explanatory - they're performing a seek on the leading column in the chosen index.

Third one is a coin-flip since, without a clustered index, both indexes have the same cost.

You might observe that the most recently-used index (from query 2) will be used for query 3 - but you shouldn't rely on it.

share|improve this answer
    
Does that mean - in case of 3rd query execution Example1_Index execution is just a random case and it can be Example_Index also ? –  RGI Jul 18 '12 at 19:50
    
It could be, yes. This is all cost-based and those two indexes have the same cost if you're using SELECT * and there is no WHERE clause. –  Aaron Bertrand Jul 18 '12 at 19:51
    
Why does SQL Server use any index in the 3rd case? –  pst Jul 18 '12 at 19:52
5  
@pst it doesn't necessarily have to. Since there is no clustered index on the table, and the index contains all of the columns in the table, it is likely also a coin-flip with a table scan. But I'd bet there's something in the optimizer code that leans toward an index. –  Aaron Bertrand Jul 18 '12 at 19:54
    
@RGI you added a screen shot of the index scan we already knew was happening. What does that change about your question, the answer, or these comments? –  Aaron Bertrand Jul 18 '12 at 19:56

There is no certainty that which index executes.

share|improve this answer
    
can you please explain a bit more...? Thanks for the reply.... –  RGI Jul 19 '12 at 17:13
    
consider a query. select * from tablename where 1=1 or 2=2 or 3=3. Do you think 2=2 or 3=3 will ever execute as in an OR statement, logically if first statement is true, it shouldn't execute the remaining statement. But sql server makes a random execution plan sometimes, according to the time taken. Similarly in your case, one can never be sure that which index would be executed/. –  user1501472 Jul 19 '12 at 17:25
    
Did you check my first two queries? I understand that these two uses the mentioned indexes in my query. Can you confirm my understanding ? –  RGI Jul 19 '12 at 17:29

Index creation is the crucial thing in DB. In large database it shows a major role during the search of particular record set.

So first of all when you search the record so on the basis of search criteria the DB engine try to search the record through index. If it find the right index the query is said to be optimized and if it doesn't then engine mark the this index as a missing index and finally provide the result set very slow.

For finding the missing index you can run :

Select * from dm_db_missing_index_details

Here you will get equality_Columns, Non_equalityColumns, and included columns. so on the basis of these details you can prepare the right index for your search criteria.

For Ex:

CREATE NONCLUSTERED INDEX IX_MyIndex ON
dbo.Mytable(ListOfEqualityColumns_separatedByCommas,ListOf_NONE_qualityColumns_separatedByCommas)
INCLUDE (ListOf_Included_Columns_separatedByCommas); 
GO
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.