Index in Sql Server

Understanding Indexes

Indexes speed up the querying process by providing filtered access to rows in the data tables (just like book’s index) if they are used efficiently.

Indexes are created on columns in tables or views.

If you create an index on the primary key and search for a row, a primary key value will be searched first and then corresponding record will be returned. Without using index, whole table will be scanned row by row(Table Scan) from first record up to last record in the table. It doesn't matter your search result has been found in between or not, search will go from first record till last record. This can have a significant effect on performance if your table has a large number of records.

You can create indexes on most of the columns in a table or a view with the exception of columns configured with large object (LOB) data types, such as image, text, and varchar(max). You can also create indexes on XML columns, but those indexes are slightly different from the basic index and are not covered in this article.

How Index Works

An index is made up of a set of pages(index nodes) that are organized in a B-tree structure. This structure is hierarchical in nature, with the root node at the top of the hierarchy and the leaf nodes at the bottom, as shown in Figure below.

In the above image, each node  (5,10,15) represents a table which is physically stored as a three pages i.e 5, 10, 15 in  Sql Server which can be visualized as image given above.

When a query is issued against an indexed column eg. (SELECT * FROM tblExamle WHERE EmployeeId=3), the query engine starts at the root node and navigates down through the intermediate nodes, with each layer of the intermediate level more granular than the one above. The query engine continues down through the index nodes until it reaches the leaf node. The leaf node will contain either the entire row of data or a pointer to that row, depending on whether the index is clustered or nonclustered.

1. Clustered Indexes   

A clustered index stores the actual data rows at the leaf level of the index.

An important characteristic of the clustered index is that the indexed values are sorted in either ascending or descending order. As a result, there can be only one clustered index on a table or view. In addition, data in a table is sorted only if a clustered index has been defined on a table.

A table that has a clustered index is referred to as a clustered table. A table that has no clustered index is referred to as a heap.

2. Nonclustered Indexes

Unlike a clustered indexed, the leaf nodes of a nonclustered index contain only the values from the indexed columns and row locators that point to the actual data rows, rather than contain the data rows themselves and hence query engine takes an additional step in order to locate the actual data.

A row locator’s structure can point to a clustered table or to a heap.

If referencing a clustered table, the row locator points to the clustered index, using the value from the clustered index to navigate to the correct data row. If referencing a heap, the row locator points to the actual data row.

Single table or view can have up to 249 & 999  non clustured indexes in Sql server 2005 & 08 resp.

In addition to being able to create multiple nonclustered indexes on a table or view, you can also add included columns to your index. This means that you can store at the leaf level not only the values from the indexed column, but also the values from non-indexed columns. This strategy allows you to get around some of the limitations on indexes. For example, you can include non-indexed columns in order to exceed the size limit of indexed columns (900 bytes in most cases).

Understand with example

Types of Index

1. Composite index

An index that contains more than one column. You can include up to 16 columns in an index. As long as the index doesn’t exceed the 900-byte limit. Both clustered and nonclustered indexes can be composite indexes.

2. Unique Index

An index that ensures the uniqueness of each value in the indexed column. If the index is a composite, the uniqueness is enforced across the columns as a whole, not on the individual columns. For example, if you were to create an index on the FirstName and LastName columns in a table, the names together must be unique, but the individual names can be duplicated.

A unique index is automatically created when you define a primary key or unique constraint:

• Primary key: When you define a primary key constraint on one or more columns, SQL Server automatically creates a unique, clustered index if a clustered index does not already exist on the table or view. However, you can override the default behavior and define a unique, nonclustered index on the primary key.
• Unique: When you define a unique constraint, SQL Server automatically creates a unique, nonclustered index. You can specify that a unique clustered index be created if a clustered index does not already exist on the table.

3. Covering index

A type of index that includes all the columns that are needed to process a particular query. For example, your query might retrieve the FirstName and LastName columns from a table, based on a value in the ContactID column. You can create a covering index that includes all three columns.

Issues with Index

1. Takes up significant disk space.
2. Indexes are automatically updated when the data rows themselves are updated, and hence can affect performance.

How to Design your Index

1. For tables that are heavily updated, use as few columns as possible in the index.
2. Table with large data but with less modification  should be heavily indexed as much as possible.
3. For clustered indexes, keep the length of the indexed columns as short as possible with primary key (Unique and NOT NULL)
4. Minimize duplicate values in a column that is indexed.
5. For composite indexes, take into consideration the order of the columns in the index definition. Columns that will be used in comparison expressions in the WHERE clause (such as WHERE FirstName = 'Charlie') should be listed first. Subsequent columns should be listed based on the uniqueness of their values, with the most unique listed first.
6. Try to insert or modify as many rows as possible in a single statement, rather than using multiple queries.
7. Create nonclustered indexes on columns used frequently in your statement’s predicates and join conditions.
8. Consider indexing columns used in exact-match queries.