SQL Indexes

Indexes

Indexing  is way to sort and search records in the table. It will improve the speed of locating and retrieval of records from the table.It can be compared with the index which we use in the book to search a particular record.

In Sql Server there are two types of Index

1) Clustered Index

2) Non Clustered Index

Clustered Index

- Clustered index physically stored the data of the table in the order of the keys values and the data is resorted every time whenever a new value is inserted or a value is updated in the column on which it is defined.

- In a table only 1 clustered index is possible.

- In a clustered Index, the leaf node contains the actual data.

Non Clustered Index

- In case of Non clustered index it create a separate list of key values (or created a table of pointers) which points towards the location of the data in the data pages.

- In a table 249 non clustered index is possible.

- In a non clustered Index, the leaf node contains the pointer to the data rows of the table.

Guidelines for Clustered Index

With few exceptions, always create clustered indexes on columns frequently used in queires.

Use Clustered Index on columns used in following queries:

1. Queries that return a range of values by using operators such as BETWEEN, >, >=, <, and <=.

2. Queries that return large result sets.

3. Queries that use JOIN clauses using Foreign Key columns.

4. Queries that use ORDER BY or GROUP BY clauses.

For example, an index on the columns specified in the ORDER BY or GROUP BY clause may remove the need for the Database Engine to sort the data, because the rows are already sorted. This improves query performance.

Use Clustered Index on following columns in a table:

1. Columns that are unique or contain many distinct values.

For example, an Author ID uniquely identifies authors. A clustered index or PRIMARY KEY constraint on the AuthorID column would improve the performance of queries that search for author information based on the Author ID.

A clustered index could also be created on author’s FirstName, MiddleName, LastName because author records are frequently grouped and queried in this way, and the combination of these columns would still provide a high degree of difference.

2. Columns that are frequently used to return a range of values by using operators such as BETWEEN, >, >=, <, and <=.

For example, a customer ID uniquely identifies customers in the Customer table. Queries which consist of sequential search, such as WHERE CustomerID BETWEEN 900 and 999, would benefit from a clustered index on CustomerID. This is because the rows would be stored in sorted order on that key column.

3. Columns that are defined as IDENTITY because these columns are guaranteed to be unique within the table.

4. Columns that are frequently used to sort the data retrieved from a table (using ORDER BY clause).

It can be a good idea to cluster (i.e. physically sort) the table on that column to save the cost of a sort operation every time the column is queried.

Clustered Indexes are NOT good for the following columns in a table:

1. Columns that undergo frequent changes.

This causes the whole row to move which affects the performance, because the Database Engine must keep the data values of a row in physical order. This is an important consideration in large-volume transaction processing systems in which data is typically large and volatile.

2. Columns used in Composite keys.

Guidelines for Non-Clustered Index

Consider creating one or more non-clustered indexes on databases or tables with low update requirements, but large volumes of data to improve query performance.

Please note that, large numbers of indexes on a table affect the performance of INSERT, UPDATE, DELETE, and MERGE statements because all indexes must be adjusted appropriately as data in the table changes. Therefore, avoid creating indexes on this kind of tables.

Use Non-Clustered Index on columns used in following queries:

1. Queries that use JOIN or GROUP BY clauses.

Create one or more non-clustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.

2. Queries that do not return large result sets.

3. Queries that contain frequently involved columns in search conditions, such as WHERE clause, which returns exact match.

Use Non-Clustered Index on following columns in a table:

1. Create non-clustered index on non-key columns that are frequently used in queries.

2. Columns with lots of distinct values, such as a combination of last name and first name, if a clustered index is used for other columns.

A table can have multiple non-clustered indexes?

A table can have multiple non-clustered indexes because they don’t affect the order in which the rows are stored on disk like clustered indexes.

Why can a table have only one clustered index?

Because a clustered index determines the order in which the rows will be stored on disk, having more than one clustered index on one table is impossible. Imagine if we have two clustered indexes on a single table – which index would determine the order in which the rows will be stored? Since the rows of a table can only be sorted to follow just one index, having more than one clustered index is not allowed.

Summary

In this article I try to explain the basic of Cursor in SQL Server with a simple example. I hope after reading this article you will be able to understand cursors in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.