Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Row Locator in Non Clustered Index

I was reading about Non Clustered Index which says that "Nonclustered index contain only the values from the indexed columns and row locators that point to the actual data rows, rather than contain the data rows themselves. This means that the query engine must take an additional step in order to locate the actual data."

Query - I am not clear with Row Locator. I am assuming that it is not any Primary key. There is something happening in background which has to do with Row-Locator to uniquely identify the row.

like image 648
Nilish Avatar asked May 29 '12 16:05

Nilish


1 Answers

If the table has a unique clustered index, the "row locator" consists of the columns of the clustered index.

With a non-unique clustered index, the "row locator" consists of the columns of the clustered index, plus a new field SQL Server adds to make the reference unique. The new field is invisible to users. It's called "uniqueifier" and consists of four bytes.

In a table without a clustered index (aka a heap), the "row locator" is a RID or row identifier. The RID points to a physical location. It consists of the file identifier (ID), page number, and number of the row on the page.

One practical consequence of the "row locator" is that it makes sense to have a small primary key for a table with a lot of indexes :) Full details on this MSDN page.

like image 119
Andomar Avatar answered Oct 30 '22 20:10

Andomar