1

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?

Cons:

  • Waste of space
  • Added complexity
  • Decreased performance on inserts

Pros:

  • 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).

closed as unclear what you're asking by George.Palacios, kevinsky, mustaccio, LowlyDBA, Colin 't Hart Aug 13 at 15:52

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    It can be, sure. For example in the case where some queries need all or some of the columns in in the existing index, and some queries only need the indexed column. – Aaron Bertrand Aug 13 at 0:51
  • 1
    FWIW, this question is definitely not a duplicate of the one you linked to in your question. – xzilla Aug 13 at 3:58
  • Index usage depends on the complexity of queries using this table. – Jakub P Aug 13 at 5:49
  • I've voted to close as your question is unclear for me at the moment - I'm not sure I can see that any answer would satisfy the question in it's current form. – George.Palacios Aug 13 at 9:43
  • 1
    @SolomonRutzky do you mean for performance reasons? That's probably true, but I've found that such small Varchar code fields, while not perfect from a performance standpoint, rarely impact the performance much. And I like the self-documenting aspect of being able to query a table in a pinch without joining elsewhere to find out what all the codes are. – Jordan Rieger Aug 13 at 18:42
1

Given these queries, is it useful to create a separate (non-clustered) index on just the BillID column?

First, note that an index on BillId will include TaxCode. The clustered index key is the row loacator for this table, so every index must contain it. And for non-unique indexes, the clustered index key columns are added as index key columns to make the index physically unique.

It just won't include the rest of the columns on the leaf pages. Such an index is sometimes useful, especially if you have a wide table and no other indexes.

For instance a COUNT(*) or paging query could use this narrow unique index instead of having to scan the whole clustered index.

But for the question as stated, no, maintaining a second copy of the key index without the two non-key columns is unlikely to be useful.

  • I wasn't aware that the non-clustered, non-unique index would also include the clustered PK fields as well, but that kind of makes sense. It also makes sense that as stated in my question, since my queries will all filter on BillId and all need to return other non-indexed fields, the clustered PK index will be the best choice, and an index on BillID alone won't be useful. – Jordan Rieger Aug 13 at 18:01

Not the answer you're looking for? Browse other questions tagged or ask your own question.