Using Index Intersection
Index Intersection is a technique built into the SQL Server engine to enable it to use more than one index on a table to satisfy a given query. To demonstrate, we need to alter the Pubs table a little, so now would be a great time to back up the database.
Backup secured? OK, let’s proceed.
First we are going to create a cut-down version of the authors table using this script:
CREATE TABLE authors_names (
id INT IDENTITY,
au_lname VARCHAR(40),
au_fname VARCHAR(40),
filler CHAR(7000)
)
GO
INSERT authors_names (au_lname, au_fname, filler)
SELECT l.au_lname, f.au_fname, ‘filler’
FROM authors l CROSS JOIN authors f
GO
ALTER TABLE authors_names ADD CONSTRAINT PK_authors_names PRIMARY KEY CLUSTERED (id)
GO
CREATE NONCLUSTERED INDEX i__au_fname ON authors_names (au_fname)
GO
CREATE NONCLUSTERED INDEX i__au_lname ON authors_names (au_lname)
GO
Note that I have takes a couple of short cuts to generate suitable test data. Apart from ignoring unnecessary columns for the test, I have used a CROSS JOIN to increase the amount of available test data. I have also added a “filler” column to use up extra space, as SQL Server will not use indexes on very small tables.
Now, if you run the three queries we used earlier against the “authors_names” table using the “Display Estimated Execution Plan” option in Query Analyzer, you should see that the all three queries use one or more indexes (remember the query against au_fname did a table scan previously).
In addition, you should see that the query that specifies both first and last names in the WHERE clause uses both the index on au_fname and the index on au_lname. This is the advantage that Index Intersection brings. It allows SQL Server to scan more than one relevant index to get the data you need, minimizing the amount of data returns and maximizing performance. Prior to SQL 7.0, this functionality was not available in SQL Server.
Points To Note
I chose to use non-clustered indexes in this example for a good reason. The optimizer will generally prefer to use a clustered index rather than an Index Intersection, so the demonstration would be less likely to work on a setup where clustered indexes are used in the query.
Whether or not a combination of single-column indexes is better than a compound index depends on the data and the queries you run against it. You should test out both cases in your database designs to see which is more efficient for your circumstances.
As with any indexed tables, it’s important to maintain the index structure and statistics regularly (or use auto-stats) to help SQL Server choose efficient indexes for each query.
]]>