Row Locator in Non Clustered Index

Question

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.

Answer 1

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.

Answer 2

@Nilish: "Candidate key" is a term used when you are deciding which column(s) to use for the primary key. And SQL doesn't know anything about that. I don't know what "alternate key" means except that it's another possible set of columns to use for a primary key.

So, there is no concept of "priorities" for these within SQL. SQL doesn't know which columns uniquely identify the record until you tell it by creating a primary key. Once you define the keys, then SQL uses them.

A table has either no indexes (a heap), or a non-unique clustered index, or a unique clustered index. There are no other possibilities. (The primary key is USUALLY a unique clustered index.)