Introduction
Indexes are used to query data quickly. Indexes optimize the performance of select statements. Indexes are associated with a table or view to speed up the retrieval of data rows from those tables or views. If the table does not have an index, then the whole table will be scanned during the data retrieval and this increases the data retrieval time. Indexes arrange and organize the data in the table and shorten the data retrieval time.
The difference in data retrieval can be understood by reading the below execution plan.
Execute the simple "Select" query on Employee2 table without index. The table has 3 rows. SQL Server scans full table in query execution and all the rows are scanned to match the "Where" condition.
Assume if the Employee2 table has millions of rows, then the execution engine will scan all the rows to match the condition which will take a lot of time to return the result.
Execute the simple "Select" query on Employee table which has a clustered index created on the id column. The table has 3 rows. The SQL Server query execution engine does not scan full table in query execution; it reads only the rows which match the condition.
Indices (Indexes) help query optimizer to find data. The query optimizer uses this index key to find the location of the data rows.
There are two types of index in SQL Server.
- Clustered index.
- Non-clustered index.
Clustered index
Clustered index physically organizes the data in a table. Clustered indexes sort and store the data based on the index key values. Clustered index key directly associates with the actual data row. Only 1 clustered index is allowed on a table. A clustered index is automatically created when a primary key is created in the table.
Index can be created using the CREATE INDEX command.
Syntax
- CREATE CLUSTERED INDEX IndexName ON TableName (ColumnName);
Example
- CREATE CLUSTERED INDEX Ind_EmpId ON Employee22222 (id);
Non-clustered index
Non- clustered index logically organizes the data. A table or view can contain multiple non-clustered indexes. The non-cluster index does not contain actual data row; it keeps the reference of the row. The Non- clustered index has the non-clustered index key which contains a pointer to the actual data row. A unique non-clustered index is automatically created when a unique key constraint is created on the column.
Syntax
- CREATE NONCLUSTERED INDEX IndexName ON TableName (ColumnName);
There can be only 1 clustered index and 249 non-clustered indexes allowed on a table in SQL Server 2005.
In SQL Server 2008 and later, there is only 1 clustered index and 999 non-clustered indexes allowed on a table.
We can find all the created index in the database using the command -
- SELECT * FROM SYS.INDEXES;
An index can be removed using the drop command.
Syntax
- DROP INDEX IndexName ON TableName;
Indexes can be created in many different forms like covered index, composite index, etc.
Covered index
The index which covers all requested columns in a query is called covering index. Index created as a composite index and including the columns which are using in the query in select, join and where clause is called covering index.
Composite index
If the index is created with a combination of two or more column is called composite index and max 32 columns can be used for combination in a composite index.
Drawbacks of poor indexing
Indexing is very important for database performance optimization but using wrong indexes and poorly written indexes can cause a performance problem also. If there are too many Indexes on the table it slows down the insert, update and delete statement because indexes also need to update on the insert, update, delete operation. For example, if some rows inserted, deleted, updated in the table then not only the data is updating, but the indexes are also updating which take more time to complete the rows inserted, deleted, updated operation.