Chapter 17. Build your own index

Erland Sommarskog

Consider this SQL query:

SELECT person_id, first_name, last_name, birth_date, email
FROM   persons WHERE email LIKE '%' + @word + '%'

You can immediately tell that the only way the SQL Server can evaluate this query is to look through every single email address, be that by scanning the table or by scanning an index on the email column. If the table is large, say, ten million rows, you can expect an execution time of at least a minute.

Imagine now that there is a requirement that in most cases the response time for a search should be only a few seconds. How could you solve this? Regular indexes do not help, nor do full-text indexes; to be efficient both require that there is no leading wildcard in the search string.

There is one way out: you have to build your own index. In this chapter I will look at three ways to do this. The first two methods more or less require SQL 2005 or later, whereas the last method is easily implementable on SQL 2000.

The database and the table

In this chapter we will work with the persons table shown in listing 1.

Listing 1. Creating the persons table and index on email

CREATE TABLE persons (    person_id  int          NOT NULL,    first_name nvarchar(50) NULL,    last_name  nvarchar(50) NOT NULL,    birth_date datetime     NULL,    email      varchar(80)  NOT NULL,    other_data char(73)     NOT NULL DEFAULT ' '    CONSTRAINT pk_persons PRIMARY KEY (person_id)) CREATE INDEX email_ix ON persons (email)

Chapter 17. Build your own index

The database and the table

Listing 1. Creating the persons table and index on email

Plain search and introducing tester_sp

Fragments and persons

Fragments and lists

Using bitmasks

Summary

About the author