Overwrite column Overview

The row version control framework is always enabled in Microsoft SQL Server and used by multiple features. In addition to the row version control-based isolation level, it is also used to support changes to the trigger and multiple active result set (MARS) sessions, as well as ONLINE index operation data reading.

The row version control-based isolation level is enabled at the database level. Any application that accesses objects with enabled databases can run queries at the following isolation level: read isolation level submitted. You can use row version control by setting the read_committed_snapshot database option to on, the following code example is as follows:

Alter database adventureworks
Set read_committed_snapshot on;

After the database is enabled for read_committed_snapshot, all queries running at the committed read isolation level will use row version control, which means that the read operation will not stop the update operation.

Introduction to covering Columns)

Note: This article is a translation of the "Query Execution" section from the book "Inside Microsoft SQL Server 2005 Query Tuning and Optimization". For details, read the original article of this book.
A heap or clustered index table is also called a "base table" and contains (overwrites) All columns of the table. In other words, a non-clustered index only contains (overwrites) SQL Server can store more rows on each page by limiting the set of columns in a non-clustered index, which obviously saves space, this improves search and scanning efficiency and reduces the number of I/O operations and the number of pages. however, for an index scan or search, it can only return the record rows of the columns covered by the index.
When overwrite columns are created, you can specify these key columns on non-clustered indexes. if the base table contains clustered indexes, each non-clustered index on the table overwrites the clustered index keys, regardless of whether they are non-clustered index key columns. in SQL Server 2005, we can use Create INDEX on non-clustered indexes... add extra key columns in the INCLUDE sub-language. Note: different from the index key, the column order in the INCLUDE is not important.
The following example shows how to create the following Architecture and objects:
Create TABLE T_heap (a int, B int, c int, d int, e int, f int)
Create index t_heap_a on t_heap (a) create index t_heap_bc on t_heap (B, c) Create index t_heap_d on t_heap (d) include (e) create unique index t_heap_f on t_heap (f) create Table t_clu (A int, B INT, C int, d int, e int, f int) create unique clustered index t_clu_a on t_clu (a) create index t_clu_ B on t_clu (B) create index t_clu_ac on t_clu (a, c) Create index t_clu_d on t_clu (d) include (e) create unique index t_clu_f on t_clu (f) the following lists the key columns and overwrite columns of each index.

Index name	Key Column	Overwrite Column
T_heap_a	A	A
T_heap_bc	B, c	B, c
T_heap_d	D	D, e
T_heap_f	F	F
T_clu_a	A	A, B, c, d, e, f
T_clu_ B	B,	A, B
T_clu_ac	A, c	A, c
T_clu_d	D,	A, d, e
T_clu_f	F	A, f

Note:Each non-clustered index key column in The T_clu table contains a clustered index key (except T_clu_f, which is a unique index ). t_clu_ac explicitly contains the first key of the index, while other indexes do not explicitly include column.
What is the difference between the actual index search and the bookmark query for the created column?
Here is an example:
Select e from t_clu where B = 2 at the beginning, this query looks like a candidate for index search. However, this index does not cover column e, therefore, index scanning or searching cannot return the values of column e. The solution is very simple. For each row obtained from a non-clustered index, we can use clustered indexes to query the values of column e. This method is called "bookmarkquery", which is a pointer to a heap or clustered index row. store the bookmarks of each row in a non-clustered index, so that each non-clustered index query always changes from a non-clustered index to the corresponding row in the base table.
Bookmark Query
In the above example, we learned how SQL Server uses index search to effectively obtain data that satisfies the predicate. However, we also know that non-clustered indexes do not cover all columns in the table. imagine if we have a predicate query on a non-clustered index key: the columns in the select query are not covered by the index. When SQL Server searches for a non-clustered index, some of the required columns will be lost. In contrast, if you perform a scan on a clustered index or a scan only, all columns will be obtained. Because you want to scan each row of the table, the operation is obviously not very effective. the following query is true:
Select [OrderId], [CustomerId] FROM [Orders] Where [OrderDate] = '2017-02-26 'the query above is the same as the query we used for index search, the only difference is that we have selected two columns: OrderID and CustomerID. the OrderDate column of the non-clustered index only overwrites the OrderID column.
SQL Server can solve this problem. For each row obtained from a non-clustered index, it may query the remaining columns contained in the clustered index (CustomerID here ), we call this operation "bookmark query ". A bookmark query is a pointer to a heap or clustered index row. SQL Server stores bookmarks for each row in non-clustered indexes, so that you can directly switch from non-clustered indexes to corresponding record rows in the base table.
SQL Server 2000 uses the specified iterator to query bookmarks. The text plan shows the index search and bookmarks query iterator:
| -- Bookmark Lookup (BOOKMARK [Bmk1000]), OBJECT [Orders]) | -- Index Seek (OBJECT [Orders]. [OrderDate]), SEEK [Orders]. [OrderDate] = Convert ([@ 1]) orDERED FORWARD) in SQL Server 2005, nested loop join and clustered index search are used, only when the base table contains a clustered index or RID query (the base table is a heap), the query plan in SQL Server 2005 is somewhat different from that in SQL Server 2000, but is logically the same. clustered index search is a type of bookmarked query implemented through the LOOKUP key or through the attribute Lookup = "1 ". the following are the graph query plans and text query plans of SQL Server 2005:
| -- Nested loops (inner join, outer references [orders]. [orderid]) | -- index seek (object [orders]. [orderdate]), seek [orders]. [orderdate] = '2014-02-26 ') ordered forward) | -- clustered index seek (object [orders]. [pk_orders]), seek [orders]. [orderid] = [orders]. [orderid]) lookup ordered forward) a bookmark query can be used in a heap or clustered index. As described above, in SQL Server 2000, the query of bookmarks on the heap is the same as that of the bookmarks on the clustered index. In SQL Server 2005, the bookmark query on the heap still uses a nested loop join operation, instead of the clustered index query, SQL Server uses a query operator called the RID operator. the RID query operator includes the search predicates used to query bookmarks on the stack. However, the heap is not an index and the RID query is not an index query.