An analysis of the influence of SQL Server focused index on nonclustered indexes _mssql

Objective

In the course of learning the SQL 2012 Basic tutorial, you will be interspersed with other content from time to time, believe that the children's shoes that read the SQL Server T-SQL Basic tutorial know that all the contents of the previous writing are not all excerpts from the contents of the book, if so it will have no meaning, The process of learning must also be a process of thinking, whether it is independent thinking or looking at the data are all thinking rather than a superficial, or a period of time will be forgetful. Short content, in-depth understanding.

Topic

Nonclustered index definition: A nonclustered index is also a B-tree structure, unlike a clustered index, where a leaf node of a B-tree has a pointer to a heap or clustered index. Do you really understand?? Can you cite an example?? In fact, this section ultimately want to express this meaning, the definition is too long, we abstract a point to define and draw the final conclusion, please look down.

Clustered index effects on nonclustered indexes

The concepts, principles, and creation of clustered and nonclustered indexes will not be described again, and if you are not familiar with this, please refer to the details of other garden friends in the garden.

First we create the test table

Use Sqlstudy
go
CREATE TABLE [dbo].[ Test] (
[ID] [int] NOT NULL, [i
] [nchar] (a) null,
[Second] [nchar] (a) null
)
go

Next we're going to create the test data

INSERT into [Sqlstudy]. [dbo]. [Test] ([Id],[first],[second])
SELECT 1, ' First1 ', ' Second1 '
UNION ALL
Select 2, ' First2 ', ' Second2 '
UNION all
Select 3, ' First3 ', ' Second3 '
UNION ALL
Select 4, ' First4 ', ' Second4 '
UNION ALL
Select 5, ' First5 ', ' Second5 '
go

We then create a clustered index on the top and second columns on the table, as follows

CREATE nonclustered INDEX [ix_mytable_nonclustered] on [dbo]. [Test] (
[A] ASC,
[Second] ASC
)

Now we're going to run two queries at the same time to see the execution plan "note": The previous article has said, please enable the plan that includes the actual execution.

SELECT ID from
[dbo].[ Test WHERE [i] = ' First1 ' and [Second] = ' Second1 '
SELECT Second from
[dbo].[ Test WHERE [i] = ' First1 ' and [Second] = ' Second1 ' Go

At this point we see the implementation plan as follows:

Through the above there is no doubt that we can draw the conclusion that query 1 is the use of the full table scan, while query 2 utilizes the nonclustered index lookup. We should have no doubt about this conclusion because the second column of the second query has previously created the amount nonclustered index, but not for the ID in Query 1, resulting in a full table scan of query 1, and Query 2 for nonclustered index lookups.

Here we create a clustered index on the column IDs on the table.

CREATE CLUSTERED INDEX [ix_mytable_clustered] on [dbo]. [Test] (
[ID] ASC
)

At this point we will run the following query:

SELECT ID from
[dbo].[ Test WHERE [i] = ' First1 ' and [Second] = ' Second1 '
SELECT Second from
[dbo].[ Test WHERE [i] = ' First1 ' and [Second] = ' Second1 ' Go

Now take a look at the query execution plan:

We created a clustered index on the column ID above. We can certainly immediately know that both are using index lookup, it is true, but, but you found no, open your eyes to see, we clearly created on the column ID is a clustered index, should theoretically be clustered index lookup is right ah, this is what we need to discuss in this article.

Discussion of

We'll summarize the problem as we create a clustered index on a column and the query returns the column, and the query condition is a column that creates a nonclustered index, and then the query execution plan for the column that created the clustered index looks up the nonclustered index, what exactly happened?

What actually happens is that the clustered index is internally referenced within the nonclustered index, and the data in the table after the clustered index is created is sorted according to physical logic, and when the clustered index is not created, the data in the table pointed to by the nonclustered index is then returned, However, once the clustered index is created, the nonclustered index is rebuilt so that the clustered index is pointed at this point in time. Speaking of this, for the Careyson. Description of nonclustered indexes: Nonclustered indexes are also a B-tree structure, unlike clustered indexes, the leaf nodes of B-trees keep pointers to the heap or clustered index. The generalization is very accurate, and if you create a clustered index, the pointer to the nonclustered index points to the clustered index, otherwise it points to the heap, which is the data in the table. So in this case, when the query creates the columns for the clustered index, it does a nonclustered index lookup.

At this point, we can conclude that when a clustered index is created on the retrieved column (only the column that creates the clustered index), the query does not use the clustered index lookup to retrieve the results, but instead uses the nonclustered index lookup to retrieve the results.

Summarize

Personally feel that we have to throw a problem before a definition, such as the definition of a nonclustered index: Nonclustered indexes are also a B-tree structure, unlike the clustered index, where the leaf nodes of B-trees keep pointers to the heap or clustered index. The first time to see this feeling nothing, generalities, feeling seems to understand, when encountering such a problem but overwhelmed, in fact, is not understanding the definition of depth or not enough, when a definition when you can cite the definition of examples or scenarios, it may be really understood. This is the end of this section, for the SQL this series will be a short content, in-depth understanding to explain, but also will be step-by-step on the query performance issues, from throwing problems to the final solution is a lot of goods.

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!