At some point we may need to look at adding indexes to our tables. Indexes can help with the performance when retrieving rows from our database.
An index allows us to help the SQL query engine to better search for commonly searched for data by giving it “hints” about the searchable columns. An index helps the query engine so that it doesn’t have to search all rows in a database by default but instead can use a more efficient looking up on indexed columns. So for example if we have an Employee database we might have a primary key on an employee number (which will usually be indexed) and we might add an index to the surname column, in the knowledge that this is often uses in queries.
Sounds great, right, but there is a downside to creating indexes and it is that they can have a detrimental affect on performance when inserting data. Because indexes need updating when new rows are added to the database. Hence when we add a new item of data the database engine may need to regenerate indexes each time.
An index is usually represented as a B-Tree structure (a balanced tree), see B-tree for examples of implementations.
Before we get started
The following examples work on SQL Server. In some cases they’ll work exactly “as is” on other databases, in some you may need to tweak things. I’ve simply not tried them out on anything other than SQL Server at this time.
Creating an index
As a naming convention, we will prefix our index name with IX_, so for example
CREATE INDEX IX_TITLE ON FlashCard (Title)
this creates an index, named IX_TITLE on the table “FlashCard” and the column “Title”. By default we’ll have created a NOCCLUSTERED index. If want to created a CLUSTERED index we need to change the SQL to
CREATE CLUSTERED INDEX IX_TITLE ON FlashCard (Title)
See CREATE INDEX (Transact-SQL) for the full syntax for creating an index using SQL Server.
According to the post Maximum Capacity Specifications for SQL Server you can create 999 nonclusted indexes per table. However you can only create one clustered index.
If we can create an index we must be able to drop one, so here goes
DROP INDEX IX_TITLE ON FlashCard
Viewing indexes
Using a UI tool such as SQL Server Management Studio we simply open the database in the Object Explorer and look at the Indexes section. This lists all the indexes on the table. In SQL we query the sys.indexes table, so for example we can use the following against SQL Server
select * from sys.indexes
or to view our recently added index
select * from sys.indexes where name = 'IX_TITLE'
CLUSTERED, NONCLUSTERED and UNIQUE indexes
In their simplest form, a NONCLUTERED index is an index which may have duplicates whereas (probably obvious) a UNIQUE index may not. UNIQUE indexes have a unique constraint on them whereas a CLUSTERED index is again unique but has the addition that data is ordered on disk to match the index.
By default your primary key will be created as a clustered index. For example this is the result of getting SQL Server Management Studio to create a CREATE script (abridged) for a simple FlashCard database
CREATE TABLE [dbo].[FlashCard]( [Id] [int] IDENTITY(1,1) NOT NULL, [Title] [nchar](20) NOT NULL, [Front] [ntext] NULL, [Back] [ntext] NULL, CONSTRAINT [PK_Card] PRIMARY KEY CLUSTERED
And ofcourse, as only one clustered index may exist per table, if you attempt to create a clustered index on a table with a primary key set as clustered (in it’s default state) you’ll get the error
Cannot create more than one clustered index on table ‘FlashCard’. Drop the existing clustered index ‘PK_Card’ before creating another.
in which case you’ll need to decide whether your primary key is best suited to be clustered, if not then you’ll need to drop the primary key constraint and then re-add it, for example
ALTER TABLE FlashCard DROP CONSTRAINT PK_Card GO ALTER TABLE FlashCard ADD CONSTRAINT PK_Card PRIMARY KEY NONCLUSTERED (Id)
or ofcourse create the constraint in the first place as nonclustered.
References
For an excellent and far more comprehensive look at indexes, check out What every developer should know about SQL performance.