# Will an index that is a partial key of the begining of another index perform any better than the longer index?

If I create an index that is a partial key of the begining of a second index, will SQL Server get any significant gains retreiving the results from the simpler index, when the search criteria matches the simpler index. For example if I had a non clustered index (TransactionDate, ClientID, State) and my search criteria is only TransactionDate and ClientID what search performance gains would I achieve by creating a second index of just TransactionDate and ClientID. Index maintenance (inserts, updates, deletes) and disk space usage is of no consideration. Low level detail of how sql server implements and utilizes indexes would be greatly appreciated.

The data types are TransactionDate DATETIME with a zero timestamp, ClientID VARCHAR(6), and State CHAR(2). The table has about 16 million rows, 100 dates with about 160,000 records for each day and 500 clients. The table is loaded daily with all the records having a new date.

-
Is your question about whether the non-clustered index (TransactionDate, ClientID) will be better than (TransactionDate, ClientID, State) for queries that have WHERE TransactionDate = @SomeDate AND ClientID = @SomeCLientID and no mention of State at all? –  ypercube May 17 '12 at 23:32
Yes. The data is being heavely searched and aggregated by Date and Client and also by Date,Client, and State. Each date (no time) contains about 160,000 records and several hundred clients. Most clients span multiple states. –  Walter May 18 '12 at 0:04
What are the exact data types of the indexed columns? –  Jon Seigel May 18 '12 at 0:05
For low level details, the best resource is Kalen Delaney's book on SQl Server Internals. –  A-K May 18 '12 at 2:55
If you need to squeeze the very best performance of selects, try out index covering. –  A-K May 18 '12 at 2:57

The reason for this is in how the B-Tree index is implemented by SQL Server. Both indexes are equally capable of satisfying the predicate (WHERE clause) and therefore locating only the rows that meet the search parameters. The only difference between the two would be how many pages in the index SQL Server would have to read in order to locate the required rows. And how many pages will ultimately be determined by the size of the index rows (Storage size in bytes of (TransactionDate, ClientID) vs. the alternative, with only the size of the State column being the difference between the two.
So, what does that mean? Whether it is looking through an index of 2 or all 3 columns, SQL Server is still only looking through the same number of pages to locate the rows, unless the State column is so large as to create a significant difference in the size of a single row, thus causing the index pages to fill considerably faster. Any noticeable performance difference would only be caused if SQL Server had to read through more levels of the index to satisfy the query (3 levels vs. 4 levels, etc) and a single column of text, especially if State is a 2 char state abbreviation, simply won't be enough to make a significant difference.