Where Do I Want To Go Today? - Included Indexes
Taking a note from Microsoft's own page, I've decided to share some of my wishes for where I want SQL Server to go. Today.
Meaning this is what I'm looking for now, the things that I'd like to see implemented in future releases. Now I missed the Yukon preview and haven't read too terribly much on it, so I may be asking for something that's coming. If it's not under NDA, let me know.
Otherwise I hope to get some feedback about whether you'd like to see this feature or not. Or maybe you've got something I've forgotten on this feature. Either way let me know. I've got a few and I'm splitting them up to keep comments focused on this one feature. If you've got a wish, send it to me with a description. I'll credit you (if you want) and add my comments before dropping it out there in front of everyone.
Smart Covering
I saw this in another RDBMS. At first I wasn't sure of the benefit, but the more I though about it, the more I thought this might really help me in some places.
Normally if I wanted to build a covering index, I'd add the columns to the index, so for some Customers table, if I wanted to cover the name and last sales amount, I'd build
CREATE INDEX [Customers_NDX]
ON [dbo].[Customers] ([CustomerName], [LastSaleAmount])
But what if I didn't want to have the name column as part of the index. Or suppose I often query this table for the sales amount, but don't want the sales amount to be part of the index. There are times when I might not want this, especially if I were indexing on something like sales amount where I often look at the MIN and MAX. Having bidirectional indexes could help here, but I wouldn't want all the columns to be bidirectional. What if I could include a column in the index for covering, but not part of the index.
So I'd have an index on the customer name, for easy name searching, but for the queries that needed the name and sales amount, I wouldn't have to traverse to the data rows themselves. The data values of the LastSalesAmount column would be included in the index rows.
Now I have to admit that I am a little scared of this. Building covering indexes, or adding data to the index could slow your system down and not many people will do the testing needed, but this something that I think could help the people that are really taxing their systems with sums, counts, etc. and need another way to get this data quickly. How often have you queried a "person" type table, customers, employees, etc. just for the id? You have the name, but need an ID. Or some other value that you do not want to index, just return.
As I said, I'm not sure of the benefits, but I think this would be a neat enhancement and ensure SQL has the features of the other major RDBMSs and then some!
Other items in this series:
- Upsert
- Real Time Defragging
- Lossless Performance
- Bidirectional Indexes
- Index Tuning Wizard
- Included Indexes
Steve Jones
©dkRanch.net May 2003