SQL Server – Does Index Intersection Work with Included Columns?

indexindex-tuningsql serversql-server-2008-r2

I've been asked by a report designer whether it is acceptable to add a new index to a table to speed up a particular report. The index would be on a single column, CreatedDate, but would have included columns.

There are already five indexes on the table and I want to avoid creating separate indexes for specific reports or queries. However, I figure a single-column index might be useful in index intersection, particularly on a column that is likely to be used in multiple queries, like CreatedDate.

What I'd like to know, to help me make my decision, is whether an index which has included columns could be used in index intersection, or whether having included columns would prevent the index from being used in index intersection.

I've tried to Google this question and haven't found any information about the effect of included columns on index intersection.

Best Answer

Included columns don't prevent the index being used for any purpose.

The query optimizer will pick the best index (or possibly a table scan) that is the most efficient for the query being compiled. Keep indexes as small as possible, while still providing the desired functionality. According to Books Online INCLUDEd columns don't affect the query optimizer's selection of an index based strictly on size, however if any given index has all columns required for a query, either as key columns or included columns, that index will be given priority.

Since the query optimizer is a fairly opaque beast, you most likely need to test the suggested index to see if it is used, and what the resulting impact is for INSERTs, UPDATEs, and DELETEs.

Related Solutions

Sql-server – Included Column versus Filtered Index

Yes, modifying all 11 NC indexes to be filtered indexes (CREATE INDEX ... ON ... WHERE DeletedDate IS NULL) would help. This way you get two advantages:

The Query Optimizer will know that any row coming from these indexes already does satisfy your query filters on DeletedDate, so it won't have to lookup the clustered index to double check the DeletedDate
All NC indexes will be 10-15% smaller in size, requiring less memory and fewer IO operations to lookup.

The trade off is that any query that looks after soft deleted rows (and there have to be some query, otherwise why do the rows exist?) will be unable to use these NC indexes.

Postgresql – Postgres full text search with multiple columns, why concat in index and not at runtime

No you don't need separate indexes. Use the weights feature. They are just a label your can query against. You can have up to four labels to query against (A-D).

--search any "field" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick'::tsquery; --true

--search B "field" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick:B'::tsquery; --false

--search B or C "fields" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick:BC'::tsquery; --true

You might want to concatenate tsvectors, so that you can separately apply weights to them and then put them together:

select
  setweight( name_column::tsvector, 'A') || setweight( phone_column::tsvector, 'B');

Best Answer

Related Solutions

Sql-server – Included Column versus Filtered Index

Postgresql – Postgres full text search with multiple columns, why concat in index and not at runtime

Related Question