index(es) on 2 columns used by "between.. and"

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

SQL Server 2005 Forums

Transact-SQL (2005)

index(es) on 2 columns used by "between.. and"

Author

Topic

bigbelly
Starting Member

39 Posts

Posted - 2008-04-08 : 02:17:32

Hi experts

I have a big table with one clustered index on one of its columns "SerialNumber". Each row of this table holds one card's information. The query on this table is

SELECT * FROM TBL1 WHERE SerialNumber = @parmSerialNumber

. The query speed is pretty good at the moment thanks to its clustered index.

Now I want to change this table to have 2 extra columns "FromSerialNumber" and "ToSerialNumber" and remove the "SerialNumber" column. In that case, earh row of this table will hold a batch of cards' information. After the change, the query will become

SELECT * FROM TBL1 WHERE @parmSerialNumber BETWEEN FromSerialNumber AND ToSerialNumber

Now my quesiton is : How should I apply index(es) on those 2 new columns? a composit index including both columns or creating a non-clustered index on each of them?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-08 : 02:20:14

I'd use a composite index, but you should test it out both ways by comparing the execution plans.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

bigbelly
Starting Member

39 Posts

Posted - 2008-04-08 : 02:35:10

Thanks tkizer. I just tried both ways. All are using table scan :-(

Do I have a better choice?

pootle_flump

1064 Posts

Posted - 2008-04-08 : 04:23:07

How many rows returned? Are the ranges mutually exclusive (i.e. not overlapping)?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources