indexes - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

indexes

Author

Topic

bill_
Starting Member

38 Posts

Posted - 2010-05-28 : 09:20:54

A table has a a column for clinic and another for visitdate.
There is an index for visitdate.

visdate clinic
----------------
12/31/2000 41
01/01/2001 02
01/01/2001 44
01/01/2001 03
01/01/2001 02
01/02/2001 08
goes on and on like this for over a million records.

I need to query for visdates in a date range for one clinic each time.

My questions are:
1) If an additional index was made
with both visdate and clinic as index key columns,
would queries speed up ?
2) If an additional index was made
with only clinic as a index key column,
would queries speed up ?
3)If the answers to 1) and 2) are "yes", which is faster ?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-28 : 09:58:53

If your query is something like
select visdate,clinic
from myTable
where visdate >= aDate and visdate <= laterDate
and clinic = '03'
an index on those two columns (called a covering index) could really help. But if you're not really limiting your results by clinic or visdate, SQL might just say "You know, it'd be faster to just scan the table than use the index". The only way to know for sure is by looking at the execution plan and testing.

Jim

Everyday I learn something that somebody else already knew

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-05-30 : 01:23:39

1) yes
2) yes
3) 1 is faster

That is for specific query as jimf provides. With other queries, it may be different.

Also, when you do covering index (or composite index), make the column that has less repeated data first.

Kristen
Test

22859 Posts

Posted - 2010-05-30 : 02:05:44

I doubt (2) will be any better. Clinic is probably not "selective" - i.e. you have only a relatively few distinct values for clinic, repeated many times, and as such SQL would not use the index.

Presumably also

there are NOT many rows for each given date? Thus an index by VisDate, Clinic would be most selective, and cover the query

But the only way to know, for sure, with your data is to test it by by checking the query plan and Logical I/O before/after creating the index.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources