This question is about the effectiveness of a SQL Server indexing technique. I think it is known as "index intersection".
I'm working with an existing SQL Server (2008) application that has a number of performance and stability issues. The developers did some odd things with indexing. I’ve not been able to get conclusive benchmarks on these issues, nor can I find any really good documentation on the internets.
There are many searchable columns on a table. The developers created a single column index on EACH of the searchable columns. The theory was that SQL Server would be able to combine (intersect) each of these indexes to efficiently access the table in most circumstances. Here is a simplified example (real table has more fields):
CREATE TABLE [dbo].[FatTable]( [id] [bigint] IDENTITY(1,1) NOT NULL, [col1] [nchar](12) NOT NULL, [col2] [int] NOT NULL, [col3] [varchar](2000) NOT NULL, ... CREATE NONCLUSTERED INDEX [IndexCol1] ON [dbo].[FatTable] ( [col1] ASC ) CREATE NONCLUSTERED INDEX [IndexCol2] ON [dbo].[FatTable] ( [col2] ASC ) select * from fattable where col1 = '2004IN' select * from fattable where col1 = '2004IN' and col2 = 4
I think multiple column indexes targeted to search criteria are much better, but I may be wrong. I have seen query plans that show SQL Server doing a hash match on two index seeks. Perhaps this makes sense when you don’t know how the table is searched? Thanks.