Do I need 2 separate indexes or just 1 composite index is OK?

[mb22]

i have a table that I select by the main id, and then sort say ... by the sequence number

Cust_ID Seq_No
------- ------
1111 1
1111 2

2222 1

3333 1
3333 2
3333 3

etc.. etc ...

i usually use a query like

select * from Customers where Cust_ID=3333 order by Seq_No
.... and when I link to other tables .. I link by the Cust_ID only.

Can I use only one composite index which can handle both the SELECT and SORT quickly like ...

CREATE INDEX IX_CustID ON Customers(Cust_ID, Seq_No)

or do I need to have 2 separate indexs

CREATE INDEX IX_CustID ON Customers(Cust_ID)
CREATE INDEX IX_CustID ON Customers(Cust_ID, Seq_No)

 

[mrdenny]

So you have a clustered index on the table anywhere? I would recommend using a clustered index across the Cust_ID and SEQ_No columns. This will handle your sorting automatically saving you CPU cycles on each query, and will also group the Cust_ID records together.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[mb22]

No. I do not have a clustered index on the table anywhere.

Do you mean i should just create a single index like this to handle all my SELECT, SORT and UPDATE operations?

CREATE UNIQUE CLUSTERED INDEX IX_CustID_Seq_No
ON Customers(Cust_ID, Seq_No)
GO


I only need just this one index , correct?

 

[mrdenny]

Correct. I would give that a try. It should do the trick for you.

If you don't need this index to enforce uniqueness you can remove the UNIQUE from the CREATE UNIQUE CLUSTERED INDEX.

Denny

--Anything is possible. All it takes is a little research. (Me)