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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With