Indexing in SQL Server can make lots of difference in performance - efficient indexing can make the application light and fast. Indexing in SQL Server can make lots of difference in performance - efficient indexing can make the application light and fast. Poor indexing can slow the application miserably. The general indexing strategy should be to establish indexes that are not necessarily query specific, but instead provide the best performance for the overall workload against the database. For selecting the efficient set of indexes, clear understanding of uses of database and data it contains are required.
While defining a perfect set of indexes, you will ensure that the most significant queries are able to read only the minimum required amount of data, and in a logical fashion and enabling them to return that data efficiently with minimal read I/O.
Details of Indexes in SQL Server are:
Clustered Index:
A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values.
Syntax for creating a Clustered Index:
CREATE CLUSTERED INDEX Index_Name ON Schema.TableName(Columnname);
Non-Clustered Index:
Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
Syntax for creating a Non-Clustered Index:
CREATE NONCLUSTERED INDEX Index_Name ON Schema.TableName(ColumnName);
Non-Covering:
A non-covering Index is an index that does not contain all of the columns that need to be returned by a query, forcing the query optimizer to not only read the index, but also read the clustered index to gather all the data it needs so it can be return.
Include Index:
It allow creation of non-clustered indexes that contain non-key columns as a part of the index definition, so that a single index can cover more queries. The key columns of an index are stored at all levels of the index, but the included columns are only stored at the leaf level of the index. The INCLUDE attribute was added to indexes in SQL Server 2005 specifically to solve problems of this type. It allows you to add a column to the index, for storage only, not making it a part of the index itself, therefore not affecting the sorting or lookup values of the index. Adding the columns needed by the query can turn the index into a covering index, eliminating the need for the lookup operation. This does come at the cost of added disk space and additional overhead for the server to maintain the index, so due consideration must be paid prior to implementing this as a solution. Included columns can only be created on non-clustered indexes, and the non-key columns do not count towards the limitation of 900 byte key size or 16-columns that exists in SQL Server.
Key Benefits:
The gains in query performance that included columns can provide must be balanced against the cost of higher disk space requirements, lower cache efficiency, and reduced performance of data modification operations.
