Covering Index

A covering index is any index that completely meets the needs of a specific select query. As SQL Server chooses which index to use to locate the data, it’s possible that every column needed by the query is present in a nonclustered index; in this case, the relational engine retrieves the data from the index pages and never has to read from the data pages. This significantly reduces I/O by reading from a narrower table that includes more data rows per page, avoiding additional reads from the data pages.

          The nonclustered index must be able to refer to the data pages; it must include the clustered index key columns in their leaf nodes (assuming the table has a clustered index). This effectively includes the clustered index columns at the end of every nonclustered index.

          A covering index may need to include some columns that are not required for the relational engine to identify the rows requested by the query’s where clause. These extra, non-key columns don’t need to be sorted in the b-tree index—they are included solely for the purpose of returning the columns in the select query.

                                                                   To specify the non-key, non-searchable columns for nonclustered indexes, use the include option. These non-key columns will not be sorted as part of the index’s b-tree structure and are only included in the index’s leaf node.

                             Included columns are not counted in the nonclustered index limitation of 16 key columns and 900 bytes. In fact, up to 1,023 non-key columns may be included in a covering index. In addition, XML, varchar(max), nvarchar(max), and varbinary(max) data type columns may be added as included columns, while they are not permitted as key columns.

Using the INCLUDE keyword gives you the ability to add columns to cover a query without including those columns in the index pages, and thus without causing overhead in the use of the index. Instead, the data in the INCLUDE columns is added only to the leaf pages of the index. These INCLUDE columns won’t help in index seeking, but they do eliminate the need to go to the data pages to get the data being sought.

Too many indexes with overly large keys were created to cover a query to avoid accessing the base table and were generally only good for one situation, wasting valuable resources. Now, using INCLUDE, you get the benefits of a covering index without the overhead of bloating the non-leaf pages of the index with values that are useless from a row-accessing standpoint. Be careful not to use covering indexes unless you can see a large benefit from them. The INCLUDE feature costs less to maintain than including the values in the index structure, but it doesn’t make the index structure free to maintain.

Always need to eliminate the sort operations. Scans are bad for where clause, not for order by, group by.

It’s important for the performance of your systems that you use unique indexes whenever possible, as they enhance the SQL Server optimizer’s chances of predicting how many rows will be returned from a query that uses the index. If the index is unique, the maximum number of rows that can be returned from a query that requires equality is one. This is common when working with joins.

                             A table column may not be dropped if it is an include column in a covering index. The covering index must be dropped before the table’s column can be dropped.

Ex: create nonclustered index testind on player1(playerid) include (runs)

A primary key or a unique constraint automatically creates a unique index.

          The “ignore duplicate key” option directs insert transactions to succeed for all rows accepted by the unique index, and to ignore any rows that violate the unique index. This option does not break the unique index. Duplicates are still kept out of the table, so the consistency of the database is intact, but the atomicity of the transaction is violated.

Sy: create unique index ind on play(playerid) with ignore_dup_key

Sy: alter index in1 on custmast disable

To re-enable an index, use the alter index... rebuild with command:

ALTER INDEX [PK__Contact__0BC6C43E]

ON [dbo].[Contact]

REBUILD WITH

( PAD_INDEX = OFF,

STATISTICS_NORECOMPUTE = OFF,

ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON,

SORT_IN_TEMPDB = OFF,

ONLINE = OFF )