Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2007-03-21 : 09:59:48
|
| Hi,There is a table which I regularly run a select query on.The select query always has a fixed where clause on only three of the columns with different parameters.This is a query that runs each time:select * from tblDatawhere PersonNo = 2 and EmployeeType = 4 and DataDate = getdate()This are the types of indexes the table currently has:One index for each of these three fields i.e. index1 for PersonNoindex2 for EmployeeTypeindex3 for DataDateIn addition to the above, I also have created a covering index as followsindex4 for PersonNo,EmployeeType,DataDateIs what I have enough for indexes on this table please?Is there anything else I have to do on indexing this table?Thanks |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-03-21 : 10:07:26
|
| If you already have separate indexes for each of the three columns, you don't need another composite index for all three columns.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-21 : 10:10:26
|
| index4 is not a covering index. A covering index is one that includes all the columns used in the query and since your query uses SELECT *a covering index would have to include all of the columns in the table. If you don not actually use every single column then specify the column in your query. I'm not necessarily saying that having an index on those three columns is bad, just that it is not a covering index. The only way to truly cover a query that uses SELECT *, is to create a clustered index on those columns but that may well not be the best use of the one clustered index that you get for the table.Are you regularly updating the statistics on the table and rebuilding the indexes, because that too will greatly change the effectiveness of your indexes. |
|
|
|
|
|
|
indexing