Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using Index Intersections

By Neil Boyle,

Using Index Intersection

Introduction

First introduced in SQL Server version 7, Index Intersection gives you new options for creating indexes on tables to maximize performance

To start with I am going to pick the authors table from the Pubs database and explain the indexes that exist on that table, then look at how we would use indexes on that table for queries prior to Index Intersection, then we will look at the options that Index Intersection gives us.

A cautionary note

In a way, the authors table is both a good and a bad example for this article. It is great for demonstration purposes because the Pubs database predates SQL Server 7 and so we can see how indexes were chosen without the benefit of Index Intersection. It is also well known and available to everyone. On the other hand, the performance gains from using Index Intersection (if any) for a table of this size and design are fairly negligible.

Without Index Intersection

Imagine you have a table with two columns that you search on regularly as a pair – for example:

use pubs

go

select *

from authors

where au_fname = 'Akiko' AND

au_lname = 'Yokomoto'

The pubs database has a non-clustered compound index (i.e. an index with more than one column) on this table which suits this query perfectly, because the index is defined on the columns au_lname and au_fname in that order. SQL Server will use this index to return results for this query.

The ordering of the indexes columns is important because to use a Compound Index, the leftmost column of the index must be considered in the Where clause (or the Join clause of a multi-table query) Because of this, SQL Server will handle the following two queries in different ways.

select *

from authors

where au_lname = 'Yokomoto'

select *

from authors

where au_fname = 'Akiko'

The first query from this pair will use the same index to search the table as the first example, because au_lname is the first column defined in the index. However, SQL Server cannot use the same index for the second query, because au_fname is not leftmost in the index definition, and so the optimizer will pick another execution plan, or do a full table scan (au_fname is normally not indexed)

 

With 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)

)

insert authors_names (au_lname, au_fname, filler)

select l.au_lname,

f.au_fname,

'filler'

from authors l CROSS JOIN authors f

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)

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 (see this article for further information). 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 Analyser, 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 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 ever with 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.

About the author

Neil Boyle is an independent SQL Server consultant working out of London, England. Neil's free SQL Server guide is available on-line at http://www.impetus-sql.co.uk

Total article views: 5229 | Views in the last 30 days: 5
 
Related Articles
BLOG

Querying Microsoft SQL Server : Basics of Indexes in SQL Server

Querying Microsoft SQL Server : Basics of Indexes in SQL Server: Indexes in SQL Server: If you see...

FORUM

Index

index

ARTICLE

Stairway to SQL Server Indexes: Level 9, Reading Query Plans

Determining how, and if, SQL Server is using your indexes.

BLOG

Basics of Indexes in SQL Server

Querying Microsoft SQL Server : Basics of Indexes in SQL Server: Indexes in SQL Server: If you see...

FORUM

SQL Server Index

SQL Server Index

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones