Sunny Books
What we have

SQL indexes and index types

Indexes are used by queries to find data from tables quickly. Indexes are created on tables and views. Index on a table or a view is very similar to an index that we find in a book.

The existence of the right indexes can improve the performance of the query. If there is no index to help the query, then the query engine checks every row in the table from the beginning to the end. This is called table scan, which is bad for performance.

An example:

SELECT * 
FROM em.employee
WHERE salary>7500 AND salary < 8000 

Without index

At the moment, the employee table does not have an index on "salary" column, to find all the employees who has salary greater than 7500 and less than 8000, the query engine has to check each and every row in the table, resulting in a table scan, which can affect the performance, especially if the table is large. Since there is no index to help the query, the query engine performs an entire table scan.

With index

The index stores salary of each employee in the ascending order as shown bellow. The actual index may look slightly different.

Salary RowAddress
7000RowAddress
7200RowAddress
7300RowAddress
7500RowAddress
7600RowAddress
7800RowAddress
8000RowAddress

Now when the SQL server has to execute the same query, it has an index on the salary column to help this query. Salaries between 7500 and 8000 are usually present at the bottom since the salaries are arranged in an ascending order. SQL server picks up the row addresses from the index and directly fetch the records from the table, rather than scaning each row in the table. This is called index seek.

Create and drop index

CREATE INDEX idx_Employee_Salary
ON em.employee (salary ASC)

DROP INDEX em.employee.idx_Employee_Salary

Index types

There are several index types: clustered, nonclustered, unique, filtered, XML, full text, spacial, columnstore, index with included columns, index on computed columns.

Clusterd and nonclustered index

Clustered Index

A clustered index determines the physical order of data in a table. For this reason, a table can have only one clustered index. The primary key constraint creates clustered indexes automatically if no clustered index already exists on the table.

A clustered index is analogous to a telephone directory, where the data is arranged by the last name. A table can have only one clustered index, however, the index can contain multiple columns ( a composite index).

Create a composite clustered index: (note we have to drop the primary key constraint first)

CREATE CLUSTERED INDEX idx_Employee_name_salary
ON em.employee (name DESC, salary ASC)

Note that when query "SELECT * FROM em.employee", the order of rows change according to the index on nameDESC and salary ASC.

NonClustered Index

A nonclustered index is analogous to an index in a textbook. The data is stored in one place and the index in another place. The index will have pointers to the storage location of the data.

Since the nonclustered index is stored separately from the actural data, a table can have more than one nonclustered index. In the index itself, the data is stored in an ascending or descending order of the index key, which does not influence the storage of data in the table.

CREATE NONCLUSTERED INDEX idx_Employee_name_salary
ON em.employee (name DESC, salary ASC)

The difference between clustered and nonclustered index

1. A table can have only one clustered index but it can have multiple nonclustered indexes.

2. Clustered index is faster than nonclustered index, because the clustered index has to refer back to the table if theselected column is not present in the index.

3. Clustered index determines the storage order of rows in the table, it does not require additional disk space. Nonclustered index is stored separately from the table, additional storage space is required.

Unique and nonunique index

Unique index is used to enforce uniqueness of key values in the index.. by default primary key constraint creates a unique clustered indexc. Uniqueness is a property of an index, both clustered and nonclustered indexes can be unique.

The difference between unique constraint and unique index: there are no major difference between a unique constraint and a unique index. In fact, when you add a unique constraint, a unique index is created behind the scenes.

There are two ways to create unique index: add unique constraint or create unique index directly.

When to create a unique constraint over a unique index? To make our intentions clear, create a unique constraint when data integrity is the objective. This makes the objective of the index very clear. In either cases, data is validated in the same manner, and the query optimizer does not differentiate between a unique index created by a unique constraint or manually created.

Useful notes:

1. By default, a PRIMARY KEY constraint creates a unique clustered index, where as a UNIQUE constraint creates a unique Non-Clustered index.

2. A UNIQUE constraint or a UNIQUE index cannot be created on an existing table if the table contains duplicate values in the key columns. To solve this, remove the key columns from the index definition or delete or update the duplicate values.

Advantages and disadvantages of indexes

Advantages

Indexes are used by queries to find data quickly

1. SELECT statement with a WHERE clause

2. DELETE and UPDATE statement

3. ORDER BY ASC, ORDER BY DESC

4. GROUP BY


Disadvantages:

1. Additional disk space: clustered index does not require and additional storage while every non-clustered index requires additional space as it is stored separately from the table. The amount of space required depends on the size of the table and the number and types of columns used in the index.

2. Insert, update and delete statements can become slow: when INSERT, UPDATE, and DELETE statements modify data in a table, the data in all the indexes also needs to be updated. Indexes can help to search and locate the rows that we want to delete, but too many indexes to update can actually hurt the performance of data modifications.

3. A covering query: if all the columns that you have requested in the SELECT clause of the query are present in the index, then there is no need to look up in the table again. The requested columns data can be returned from the index.

4. A clustered index always covers a query since it cantains all of the data in a table. I composite index is an index on two or more columns. Both clustered and non-clustered indexes can be composite indexes. To a certain extent, a composite index can cover a query.

SUNWEB EXPERT