Overview of Indexes

Overview

Indexes are used to speed up searches. Properly used indexes can greatly increase search speeds, but they can also slow updates and deletes. Whenever the values in the tables change the index must be recreated. This can take resources and time.

Kinds of Indexes

Clustered indexes

A clustered index physically organizes the data by the indexed fields. If it is a character type the records with be ordered A-Z, if numeric 123... etc. Consequently, there can only be one clustered index per table. By Default, the primary key is given a clustered index, but you can override this. Below is an example of creating a table with the social security number used as a clustered index and the primary key as a non clustered index:

---

CREATE TABLE Employee
(
  EmployeeID INT IDENTITYy(1,1),
  SocialSecurityNumber NCHAR(10) NOT NULL UNIQUE,
  EmployeeName NVARCHARr(255),
  HireDate Date
)

ALTER TABLE Employee
ADD CONSTRAINT pk_Employee 
  PRIMARY KEY NONCLUSTERED(EmployeeID)

CREATE CLUSTERED INDEX ix_SocialSecurity 
     ON Employee (SocialSecurityNumber)

---

Most indexes are non clustered. Non clustered indexes create a separate structure called a Balanced Tree or B-Tree. Below is an image of a B-Tree Cluster:

Below is the code for creating a non clustered index. "NONCLUSTERED" is optional. The second option is for an index with an included column.

---

CREATE NONCLUSTERED INDEX ix_EmployeeName ON Employee(EmployeeName)
CREATE NONCLUSTERED INDEX ix_NameDate ON Employee (EmployeeName) 
INCLUDE (HireDate)

---

Filtered Index

You can create an index which filters for certain conditions:

---

CREATE NONCLUSTERED INDEX ix_HireDate ON Employee (HireDate) 
WHERE Hiredate IS NOT NULL

---

Disabling, Rebuilding, and Dropping Indexes.

When loading bulk data or testing sometimes it is necessary to disable an index temporarily. When you are done you can rebuild it. You can also drop an index completely

---

ALTER INDEX ix_EmployeeName ON Employee DISABLE
ALTER INDEX ix_EmployeeName ON Employee REBUILD

DROP INDEX ix_EmployeeName ON Employee

---

Forcing an Index

Even if you make an index, the database engine may not use it. Generally it is faster to just sort through the records until a certain threshold (several thousand) records are present. For testing purposes you can force SQL Server to use an index with the following syntax

---

SELECT * FROM Employee 
WHERE EmployeeName='John Smith' 
ITH (NOLOCK, INDEX(ix_EmployeeName))