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 |
|
maximus_vj
Yak Posting Veteran
88 Posts |
 Posted - 2005-05-25 : 18:12:35
|
| Hello,I have a table with two columns as a primary key. So by default a composite clustered index is created. My Table schema is tbl_test(ID1 int (PK),ID2 int (PK), name varchar(50))Index is created on (ID1,ID2). Index is used for the following query:select * from tbl_test where ID1 = 10Index is NOT USED for the following query:select * from tbl_test where ID2 = 10If I change the order of the index, (ID2,ID1) thenIndex is used for the following query:select * from tbl_test where ID2 = 10Index is NOT USED for the following query:select * from tbl_test where ID1 = 10What should I do so that index is used in both these queries?Thanksmaximus |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-05-25 : 19:27:37
|
| You can't use the same index for both queries because indexes are only useful based on the field order they are specified in the CREATE INDEX statement. However, you could create a second (non-clustered) index on the second field so that an index is still used. It just won't be the clustered one.---------------------------EmeraldCityDomains.com |
 |
|
|
|
|
|
Clustered Index on Composite PK