6

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.

Nilish
  • 1,066
  • 3
  • 12
  • 26

2 Answers2

11

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.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • @PankajGarg: It's actually the `clustered index`, see [MSDN](http://msdn.microsoft.com/en-us/library/ms188783.aspx): "Nonclustered indexes always contain the clustered index columns if a clustered index is defined on the table." – Andomar May 29 '12 at 16:42
  • Does it include any Priorities when collecting the Primary Key column information for Row Locator? I am asking this because, there can me Primary/Alternate/Candidate key and all these uniquely identifies the row. – Nilish May 29 '12 at 16:50
0

@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.)

David Walker
  • 101
  • 2