October 3, 2012 at 3:21 am
hi experts,
please find attachment for question, and answer is first one.
i just want to understand why is this so? can any body explain it properly?
October 3, 2012 at 3:32 am
All down to the order the columns are defined in the index, vs how you reference them in the SQL statement
October 3, 2012 at 3:34 am
It's because the seek doesn't use Id, & Id is the first column in the index.
An index can only be used for an index seek if the key to seek includes the first column in the index. The other 3 do include it (the order isn't so important).
October 3, 2012 at 3:42 am
http://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/, read the poor index design section for index key column order
October 3, 2012 at 3:49 am
ok got it, meaning is which select query will have "id" in where clause, that one will have seek operation others will have scan operation.
that is clear but, what will be the difference in these two indexes.
1) create nonclustered index NC_T2 on T(id)
2) create nonclustered index NC_T on T(id,ch,na)
how second one index benefits??
October 3, 2012 at 3:50 am
It's quite "funny" ao SQL works on this...
Since SQL doesn't know Id is always > 0 he doesn't "optimize" the query by adding a "neutral" statement that would allow the use of index seek... If you add " AND Id > 0" on the 1st statement instead of an index scan you get an index seek, even if Id > 0 represents all records...
Pedro
October 3, 2012 at 3:55 am
Zeal-DBA (10/3/2012)
ok got it, meaning is which select query will have "id" in where clause, that one will have seek operation others will have scan operation.that is clear but, what will be the difference in these two indexes.
1) create nonclustered index NC_T2 on T(id)
2) create nonclustered index NC_T on T(id,ch,na)
how second one index benefits??
the 1st one is only used when the where clause has the Id column and if you have many rows and want another column on the output you probably will end up with a Key Lookup or RID Lookup (depending you have a clustered index or not).
The 2nd one searches by id and if the column in the output is also on the index SQL gets the value from the index, doesn't need to do a Lookup on the clustered or heap (pages where data is stored). Also the 2nd index is used as seek if the other columns are on the WHERE statement.. the 1st one only when id is used.
Pedro
October 3, 2012 at 3:55 am
Its about covering indexes. If a query only uses those 3 columns, SQL Server may use the index to get the data & not look at the table at all. This is called a covering index (for a specific query). This will normally happen where the query is relatively selective.
If you only have the index with id, it may use the index but will have to do a lookup into the table to get the other two columns.
Lookups are slow & resource-intensive, so the covering index is potentially much better.
If the query returns the whole table though, SQL Server may not use any index.
October 3, 2012 at 4:05 am
laurie,
your last statement is if i fetch complete table sql is not using any of the index,
thats correct its not using any index, but why is this so table having 3 columns and index also using all three column. then why it will go for table scan in this case??
October 3, 2012 at 4:10 am
That's up to the optimiser. If it decides there's no benefit in using an index, it won't use it.
October 3, 2012 at 4:43 am
got clear lot of things.
that mean is these indexes both are same and will work same way.
create nonclustered index NC_T2 on T(id,ch,na)
CREATE NONCLUSTERED INDEX [NC_T] ON [dbo].[T] (id )
INCLUDE (ch,na)
October 3, 2012 at 4:46 am
No they are two completly different indexes, one will work if you only ever search on the ID but want to show CH, NA in the result set the other works when you search on ID CH NA.
October 3, 2012 at 4:49 am
No they're not the same.
The first will allow seeking on all 3 columns, while the second will only allow seeking on id - although the optimiser may still use the index even if you seek on id & ch or id, ch & na - of course in this case the seek cannot be entirely indexed.
If you had a query like this:
SELECT id, ch, na
FROM [dbo].[T]
WHERE id = @id
the first index could be used because it includes all columns used in the query, but the second index also 'covers' it - all 3 columns are in the key or the included columns, & the query key matches the index key.
October 3, 2012 at 5:11 am
one last question,
suppose i am using select queries like this frequently
select * from T where id =6
select * from T where ch ='!'
select * from T where na ='sue'
so i'll have to create all 3 indexes like
CREATE NONCLUSTERED INDEX ind1 ON T (id,ch,na)
CREATE NONCLUSTERED INDEX ind1 ON T (ch,id,na)
CREATE NONCLUSTERED INDEX ind1 ON T (na,id,ch)
to get better performance or incex seek for all the queries ??
October 3, 2012 at 5:26 am
Zeal-DBA (10/3/2012)
one last question,suppose i am using select queries like this frequently
select * from T where id =6
select * from T where ch ='!'
select * from T where na ='sue'
so i'll have to create all 3 indexes like
CREATE NONCLUSTERED INDEX ind1 ON T (id,ch,na)
CREATE NONCLUSTERED INDEX ind1 ON T (ch,id,na)
CREATE NONCLUSTERED INDEX ind1 ON T (na,id,ch)
to get better performance or index seek for all the queries ??
No...
First mistake... Don't use SELECT *.. if there are more fields on the table it will force a Lookup!
The 3 indexes are equal... no difference between them.
To have an index seek on them create either 3 indexes: one with id, other with ch, other with na. but all covering the output columns.
Or just create one index with 3 columns and add a neutral operation for each column not being queried.. Id > 0,....
Pedro
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply