Analyze the influence of SQL Server focusing on index on non-clustered index, and analyze SQL

Analyze the influence of SQL Server focusing on index on non-clustered index, and analyze SQL

Preface

In the process of learning SQL 2012 basic tutorial will occasionally interspersed with other content to explain, I believe that I have read SQL Server 2012 T-SQL basic tutorial of the children's shoes know that all the content written in front of is not the contents of the excerpt from the book, if this is the case, it will have no meaning. The learning process must also be a process of thinking. Whether it is independent thinking or data query, it is a process of thinking rather than looking at it, otherwise, you will be forgetful after a while. Brief content and in-depth understanding.

Topic

Non-clustered index definition: A non-clustered index is also a B-tree structure. Unlike clustered indexes, the leaf nodes of the B-tree store pointers to heap or clustered indexes. Do you really understand it ?? Can you give an example ?? In fact, what I want to express in this section is that the definition is too long. Let's abstract a little and draw a final conclusion. Please refer to it.

Influence of clustered index on non-clustered Index

The concepts, principles, and creation of clustered and non-clustered indexes will not be described. If you are not familiar with this, please refer to the details of other park friends.

First, create a test table.

USE SQLStudyGOCREATE TABLE [dbo].[Test]([ID] [int] NOT NULL,[First] [nchar](10) NULL,[Second] [nchar](10) NULL)GO

Next, create the test data.

INSERT INTO [SQLStudy].[dbo].[Test] ([ID],[First],[Second])SELECT 1,'First1','Second1'UNION ALLSELECT 2,'First2','Second2'UNION ALLSELECT 3,'First3','Second3'UNION ALLSELECT 4,'First4','Second4'UNION ALLSELECT 5,'First5','Second5'GO

Then we create a clustered index for the First and Second columns on the table, as shown below:

CREATE NONCLUSTERED INDEX [IX_MyTable_NonClustered] ON [dbo].[Test] ([First] ASC,[Second] ASC)

Now let's run two queries at the same time to see their execution plan [note]: As mentioned in the previous article, please enable the plan including the actual execution.

SELECT IDFROM [dbo].[Test] WHERE [First] = 'First1' AND [Second] = 'Second1'SELECT SecondFROM [dbo].[Test] WHERE [First] = 'First1' AND [Second] = 'Second1'GO

The execution plan is as follows:

Through the above, we can draw a conclusion: Query 1 uses the full table scan, while query 2 uses the non-clustered index search. We should have no doubt about this conclusion, because the Second column for the Second query has already created a non-clustered index before this, but there is no ID in query 1, therefore, a full table scan of query 1 is performed, while query 2 is a non-clustered index query.

Next we will create a clustered index for the column IDs on the table.

CREATE CLUSTERED INDEX [IX_MyTable_Clustered] ON [dbo].[Test] ([ID] ASC)

Run the following query:

SELECT IDFROM [dbo].[Test] WHERE [First] = 'First1' AND [Second] = 'Second1'SELECT SecondFROM [dbo].[Test] WHERE [First] = 'First1' AND [Second] = 'Second1'GO

Now let's look at the query execution plan:

Through the above we have created a clustered index for the column ID, we will be able to immediately know that both of them are using index search, which is true, but you do not find it, open your eyes to see, we clearly create a clustered index on the column ID. In theory, it should be a clustered index search. This is what we need to discuss in this article.

Problem Discussion

We will give an overview of the problem as follows. When we create a clustered index on a column and the query returns this column, the query condition is that a column with a non-clustered index is created, at this time, the query execution plan for the columns that created the clustered index is not a clustered index query. What happened in this case?

In actual situations, non-clustered indexes reference clustered indexes. After a clustered index is created, the data in the table is sorted by physical logic, when the clustered index is not created, the data in the table to which the non-clustered index points is returned, however, once a clustered index is created, the non-clustered index will be re-created, pointing to the clustered index. Here we will describe the non-clustered index of yuanyou CareySon: the non-clustered index is also a B-tree structure. Unlike the clustered index, the leaf node of the B-tree stores pointers to heap or clustered indexes. The generalization is very accurate. If a clustered index is created, the non-clustered index Pointer Points to the clustered index. Otherwise, it points to the heap, that is, the data in the table. In this case, a non-clustered index query is performed when a column with a clustered index is created.

So far, we can conclude that when a clustered index is created on the retrieved column (only the column that creates the clustered index is returned ), in this case, the query does not use clustered index lookup to retrieve results, but uses non-clustered index lookup to retrieve results.

Summary

I personally think we have to throw such a problem before defining a non-clustered index. For example, the non-clustered index is also a B-tree structure, which is different from the clustered index, leaf nodes of Tree B store pointers to heap or clustered indexes. The first time I saw this sentence, I felt like I had understood it. When I encountered such a problem, I was at a loss. In fact, I did not have enough in-depth or thorough understanding of the definition, when a definition is made, you can give an example or scenario of the definition, which can be truly understood. This section ends with a brief and in-depth understanding of the SQL series. At the same time, we will gradually discuss query performance issues, A large number of items are received only when a problem is thrown out to the final solution.

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, you can leave a message and share it with us!