Shulou(Shulou.com)05/31 Report--

This article will explain in detail about the role of nonclustered index in Sql Server, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

One: phenomenon

Let's take a look at the nonclustered index and see what it looks like in the execution plan. I have a product table here, which contains more than 8w of data, and then builds it on the Name column.

A nonclustered index, like the following figure:

From the picture above, I see two interesting things, one is the "index search [nonclustered]" that I want to see, which everyone is very familiar with, and that's what this article is about, and then we also see a "RID search". At first glance, what kind of chicken is this? no, no, no. What does nonclustered index have to do with it?

Two: what is RID?

Through the previous articles, I think you all know how to find the records in the data page. The secret is determined by the offset in the slot slot, so the question is, if I go up to the data page level, I only need (pageID:slotID) to find the record, right? So if I go up the file level, do I just need to know (fileID:pageID:slotID) to find the records in the data page? In fact, the RID here is standing at the height of the file through (fileID:pageID:slotID) to find the table record. Since RID=RowID= (fileID:pageID:slotID), if you have to see it, l does provide such a function in sq (sys.fn_PhysLocFormatter (% physloc%%)). Let's take a look at the following figure:

Looking at the picture above, is it very exciting and clear at a glance, such as the record of productID=18088, but it is on file 1, data page 34941, slot 0, and productID=18089 is on slot 1. Well, when you know what RID is, I think you are not far away from thoroughly understanding the nonclustered index.

III: nonclustered index

One thing we must know is that nonclustered indexes can speed up lookups, and if they are as fast as table scans, they will lose the purpose of indexing. Since they can accelerate because they are the same as clustered indexes, they all play B-trees at the bottom. First of all, let's insert some sample data.

DROP TABLE dbo.PersonCREATE TABLE Person (ID INT IDENTITY,NAME CHAR) CREATE INDEX idx_Person_Name ON dbo.Person (Name) DECLARE @ ch AS INT=97WHILE @ ch

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.