SQL Index Lookup

1. Index Lookup Information

In a nonclustered index, each record is stored with a value for the index key of the nonclustered index and a copy of the index key of the clustered index

"In a table without a clustered index, it is the RID value pointing to the data page, and the key to the clustered index if there is a clustered index (when no include is used)"

So here, each record will have a [UnitPrice] and [salesorderdetailid] record, stored in the order of [UnitPrice]

SELECT Types from dbo. News WHERE types>10

Schedule: Index lookups by NEWS_CL nonclustered indexes

The Returned field contains fields that are not indexed, and SQL Server will first find all records of [Types] greater than 10 on the nonclustered index, and then find the detailed data stored on the clustered index based on the value of [NewsID]. This process can be called "Bookmark Loolup" Bookmark lookup (difficult to avoid bookmark lookup)

SELECT types,contents from dbo. News WHERE types>10

Schedule: Clustered index scan via pk_news primary key

------------------------------------- to summarize ------------------------------------------------------

(1) where Back (filtered field): determines the range of fields to be built when you build the index, and if a field is often used as a filter field, you can index it on him.

1 CREATE CLUSTERED INDEX salesorderdetail_test_cl on [dbo]. [Salesorderdetail_test] ([Salesorderdetailid])

(2) where Front (Field returned): determines the speed at which your table data is found, returns the values of other fields based on a clustered index, and finds field values in the data page without a clustered index

(3) Clustered Index Scan:a field after where the field is not indexed "include nonclustered index (on table with nonclustered index) and clustered index"

But there is a clustered index on the table, regardless of whether the table has a nonclustered index

(3) Nonclustered index Scan:The field following the where is a field with no index (nonclustered index), but the table has a nonclustered index but no clustered index

(4) Table Scan:fields following the Where are fields that are not indexed and do not have clustered and nonclustered indexes on the table

(5) Bookmark Lookup:The fields after the Where are indexed (either clustered or nonclustered), but some of the fields returned in the Where are not indexed

(whether a clustered or nonclustered index)

1. When to use statistics, statistics help to complete the complexity calculation, choose the right way of execution

In order to complete the statement as fast as possible, it is not sufficient for the light to be indexed. For the same sentence, SQL Server has a number of ways to do it.

Some methods are suitable for a small amount of data, and some methods are suitable for a large amount of data. The same method, when the amount of data is different,

There is a very big difference in complexity. Indexes can only help SQL Server find records that match the criteria. SQL Server also needs to know every operation

The amount of data to be processed in order to estimate the complexity and choose an execution plan with minimal cost. In layman's words, SQL Server should be able to

The quickest way to complete instructions is to know what the data looks like.

Statistical information creation:

There are 3 main cases

(1) When an index is created, SQL Server automatically creates statistics on the column where the index is located, so in some ways the index is twofold.

He can help SQL Server quickly find the data, and the statistics above him can also tell SQL Server the distribution of data

Add: When the index is rebuilt, the statistics of the table are also updated, so sometimes the query slows down and rebuilds the index query faster. Update of statistics is also one of the reasons

(2) The DBA can also manually create the statistics that he deems necessary by using such statements create STATISTICS

If auto create statistics is turned on automatically creating statistics, you rarely need to manually create

(3) When Sqserverl wants to use statistics on certain columns and finds no, "auto create statistics automatically creates statistics"

Causes SQL Server to automatically create statistics

For example, when a statement is filtered on a (or several) field, or if you want to make a join (join) with another table, SQL Server estimates how many records will be returned from this table.

A statistic is needed to support this. If not, SQL Server automatically creates a

On the database where auto create statistics automatically creates statistics, it is generally not necessary to worry that SQL Server does not have enough statistics to select the execution plan.

This is entirely up to SQL Server management.

SQL Index Lookup