Suppose I have this table (simplified from my project):
create table BillTax ( BillID int not null constraint FK_BillTax_Bill foreign key references Bill(BillID), TaxCode varchar(20) not null, constraint PK_BillTax primary key clustered (BillID, TaxCode), TaxRate decimal (10, 9) not null, TaxAmount decimal (12, 4) not null, );
As you can see, the primary key is a composite of the BillID and TaxCode columns and uses a clustered index for speed. All queries against the table will seek on BillID, but some queries will seek on both BillID and TaxCode. Also, all queries will include other columns (e.g. TaxRate, TaxAmount) as result data. Given these queries, is it useful to create a separate (non-clustered) index on just the BillID column?
- Waste of space
- Added complexity
- Decreased performance on inserts
- Index on a single column should be pretty small
- Query optimizer should be able to pick the best query either way (but we know the query optimizer is not always as smart as we'd want)
My understanding is that if I query this table on just the BillID column, the query optimizer will still use the clustered index because that index has the BillID column as its first column. And it will be quite fast because even though the index will be a little bigger as it includes the TaxCode column, being clustered will make up for that. On the other hand, one could argue that a single non-clustered index could be faster, if the data coming back is not that heavy. See Nonclustered index is faster than clustered index? for a related question.
I know, I know, try both and measure it yourself. I guess I'm just wondering if there is a major theoretical consideration I'm missing that renders one approach or the other moot.
Note: I found a possible duplicate here, but it deals specifically with PostgreSQL, and the accepted answer relies on some DBMS-specific implementation details like data type storage sizes: Is a composite index also good for queries on the first field? (sic).