October 11, 2009 at 8:50 am
Respected All
I have no idea how index works ,I have some confusion in index searching.
Suppose I create one index on columns like this
create index IX_1 on TableA(col1, col2, col3)
and like this
Create index IX_1 on TableA(col1)
Create index IX_2 on TableA(col2)
Create index IX_3 on TableA(col3)
what is difference and which method is best.
Thanks
Ghanshyam
October 11, 2009 at 10:14 am
creating a covering index (col1, col2,col3) will be very helpful in queries where you're using all three parameters in the where clause. however, if you try to search with a single column, the covering index wont be used ( as far as i know)
These two articles written by Gail will help you.
http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/
http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
October 11, 2009 at 11:17 am
Go to this site:
http://www.sqlservercentral.com/articles/books/65831/
Down load the free e-book SQL Server Execution Plans by Grant Fritchey
(1st Edition, June 2008) and read how execution plans can help you to decide which indexes are most useful.
Here is a extract from a description of the information contained in the e-book
Execution plans show you what's going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by SQL Server, including:
Which indexes are getting used and where no indexes are being used at all.
How the data is being retrieved, and joined, from the tables defined in your query.
October 12, 2009 at 1:10 pm
It really depends on the queries and the data in the table.
A covering index can be used for single column queries as long as the single column is the left most column.
For example, if you have this index: create index IX_1 on TableA(col1, col2, col3)
The index will be considered for use for each the following queries:
Select *
From TableA
Where col1 = 'Some Value'
And col2 = 'Some Value'
And col3 = 'Some Value'
Select *
From TableA
Where col1 = 'Some Value'
And col2 = 'Some Value'
Select *
From TableA
Where col1 = 'Some Value'
It will not be considered for use for these queries:
Select *
From TableA
Where col2 = 'Some Value'
And col3 = 'Some Value'
Select *
From TableA
Where col2 = 'Some Value'
Select *
From TableA
Where col3 = 'Some Value'
Note that I said "can be used" and "will be considered for use". I did not say "will use". SQL is smart enough (usually) to determine whether or not using an index is faster or not. You will often see SQL perform an index scan of the clustered index and look up values rather than use a nonclustered index. It all boils down to which index operation will require the least amount of read operations to get the data required.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply