A brief analysis of SQL Server focused use index and query execution plan _mssql

Objective

An analysis of the impact of SQL Server focused indexes on nonclustered indexes we talked about the effect of clustered indexes on nonclustered indexes, the performance optimization that the database has been emphasizing, so this section we talk about using the index to see how the query execution plan is, short content, in-depth understanding.

View query execution plan through index

Let's first take a look at the first example

1, the default use index

Use TSQL2012
go
select OrderID from 
sales.orders
select * from 
sales.orders

The overhead we see in the 2nd query is 3 times times the cost of the 1th query, and of course the 1th query simply returns a column and the 2nd query returns all the columns, which also consumes a small amount of performance. For SQL Server queries, the index is used internally to get the best performance from the shortest path. We are able to note that even if the OrderID is used as the primary key, the return data is not a clustered index that is automatically generated by the primary key being used. I believe there are many people subjectively feel that the return of the primary key and query no query criteria should be to take the primary key of the clustered index, but sometimes the fact is not so, the last article we have discussed this issue, no longer described. Using the * return data in the 2nd query is a clustered index that utilizes the primary key.

2. Force primary key to use clustered index

Forcing an index we use with (index name) to create, as follows:

Use TSQL2012
go
select OrderID from 
sales.orders with (INDEX (pk_orders))
select * 
from Sales.orders with (INDEX (pk_orders))

We can see from the above that the default return of the primary key column using a nonclustered index, where we force it to go to the clustered index, and for the 2nd query, needless to say, at this time the cost is quite.

3. Forcing the use of nonclustered indexes

We continue to look down and force a nonclustered index lookup on the query, as follows:

Use TSQL2012
go
select OrderID from 
sales.orders with (INDEX (idx_nc_custid))
select * 
from Sales.orders with (INDEX (Idx_nc_custid))

Visible from the above, the cost difference between the two, for queries with nonclustered indexes, query 1 returns a single column, and query 2 returns all columns in such a fast way that by using the above default index, forcing a clustered index, and forcing a nonclustered index, we know the best choice for a clustered index that uses a primary key for retrieving all the column result sets.

Summarize

With the above demo we know that even if you create a clustered index, you do not use a clustered index to retrieve the results, and sometimes using a nonclustered index provides better performance than using a clustered index, which is certainly not the same as using a scenario. When you talk about database optimization in every interview, the first thing you think about is the index, then there is no context, how to use the index, how to use different indexes in different scenes? In any database index has always been a very big topic and is a complex content, complex content are made from simple accumulation, we must be like a snail to study slowly, the cocoon stripping silk, the end will have a good effect. Short content, in-depth understanding.

The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring some help, if there are questions you can message exchange, but also hope that a lot of support cloud Habitat community!