SQL Server Overwrite Index

To better understand the coverage index, before formally covering the index, let's start by talking a little bit about the basics of indexing.

Data pages and index pages

In SQL Server, the basic unit of data storage is the page, with a page size of 8KB, consisting of the header, data rows, and row offsets, such as structure:

　　

The top page is fixed at 96 bytes to store related page system information, such as the database table object ID to which it belongs. The data row is the storage area of the real data, and the size of each row is not fixed. The row offset is an array where each position of the array occupies 2 bytes, which is used to store the offset of the position at the beginning of the data row, mainly for quick positioning, for example, to find the nth row, and to access the nth item of the row offset array to quickly find the location of the data row. Index pages are similar in structure to data pages, in that the data rows of an index page store information related to the index.

Clustered indexes and nonclustered indexes

The clustered index defines the real physical location of the data store in the table, which stores the data in the order of the specified columns, and the Chinese characters in the Xinhua dictionary are arranged in alphabetical order, so each table can only build one clustered index. A clustered index is a B + tree structure that contains index pages and data pages, the bottom row of leaf nodes is the data page, and the index page to the top, to see a picture should be clearer:

Nonclustered indexes are independent of the logic of data real storage order, which is analogous to the method of finding Chinese characters by the radicals in Xinhua dictionary. In contrast to a clustered index, a nonclustered index is also a B + tree's data structure, but contains only index pages, and multiple nonclustered indexes can be established in one table, and an in-depth analysis of the index can be viewed in this article. Also look at a graph of nonclustered indexes:

　　

What is an overlay index

The overlay index is a concept introduced in SQLServer2005, and can only be built on a nonclustered index, typically the index page of a nonclustered index does not contain real data, only a pointer to the data row in the data page, and the overwrite index is stored on the index page by storing the data. In order to find the corresponding data, as long as the index page can be found to access the data, there is no need to query the data page, so that the index is the data "overwrite".

-- Overwrite index creation is to add an include statement on the basis of nonclustered index creation CREATE nonclustered INDEX {index_name}  on -- Nonclustered indexes can declare multiple columns as index entries INCLUDE (column_name ...)         -- overriding indexes can specify that multiple columns are stored on the index page

Create an overlay index syntaxOverlay Index Analysis

This section is described by creating an overlay index and viewing the index using the DBCC command.

IF db_id('Test') is NULLBEGIN    CREATE DATABASETest;ENDGO UseTest;GOIF object_id('T1','U') is NULLBEGIN    CREATE TABLET1 (t1_idINT          not NULL IDENTITY(1,1), T1_nameVARCHAR( -) not NULL, T1_name1VARCHAR( -) not NULL, T1_name2VARCHAR( -) not NULL, T1_name3VARCHAR( -) not NULL, T1_name4VARCHAR( -) not NULL, T1_name5VARCHAR( -) not NULL    );END/** Insert test data*/INSERT  intoT1 (t1_name, t1_name1, T1_name2, T1_name3, T1_name4, T1_name5)SELECT 'name',           'name',           'name',           'name',           'name',           'name'       fromsysobjects O1 Cross JOINsysobjects O2; /** Create an overlay index*/IF  not EXISTS(SELECT 1                 fromsysindexesWHEREName='idx_t1_id')BEGIN     CREATE nonclustered INDEXidx_t1_id onT1 (t1_id) INCLUDE (t1_name);END

CREATE TABLE & test Data & Overwrite index

There are 4,000 or so test data inserted into the cross join, and you can now use the DBCC command to view the data and index pages of the table.

/* * View the basic information of the page * Prerequisites: The table must be inserted data * Required parameters: (database name, table name, 1 means to display all IAM pages, data pages, index pages) */ DBCC IND (TEST,T1,-1

View data page and index page commands for a table

After executing this command, you should see the page information displayed, where pagetype=1 rows represent data pages, pagetype=2 rows represent index pages, arbitrarily select a pagetype=2 row, find Pagefid and Pagepid, You can use the DBCC command to view the specific information for the index page.

/* * View the basic information of the index page * Required parameters: (database name, pagefid,pagepid,3 indicates output per row per column of information) */ DBCC PAGE (Test,1,7732,3);

commands to view information about index pages

After executing this command, you should see that the information in the T1_name column is contained in this index page. It is now possible to see the performance gains of the overlay index by executing different query SQL, and opening the actual execution plan while executing the SQL, so that the results of the comparison can be clearly seen.

SELECT t1_name    from  WHERE=N; SELECT t1_name1    from  WHERE=n;

Compare Query SQL

　　

　　

The query 1 cost is 33%, and the cost of Query 2 is 67%, and the comparison can see that the cost of query t1_name is much smaller than the cost of query t1_name1, because the query t1_name only need to perform the index, you can find the data on the index page, and the query t1_name1 to find the data page.

Reflection on the overlay index

Creating an index can lead to optimization of the query, but it has the burden of changing the data, and it is not surprising to overwrite the index. As we know from the above analysis, the overlay index is further refined for nonclustered indexes, and when updating the data, if it involves overwriting the columns of the index include, changing the index page in addition to changing the data page adds extra work than simply using a nonclustered index. Therefore, when designing an overlay index, consider the columns that should be overwritten to ensure that the included columns provide optimal performance optimizations.

SQL Server Overwrite Index