How to get rowid in sql server 2005

Question

How to get rowid in sql server 2005

Viewing 15 posts - 16 through 30 (of 40 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150468 More actions · Answer 1

GilaMonster (3/28/2010)
SwePeso (3/28/2010)
All records do have a "hidden" record identifier, when there is not primary key on the table.
I assume you mean 'clustered index', not 'primary key'.
The RID's there on all tables, cluster and heap.

I assume you mean 'row locator', not 'RID' 😀

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Paul White NZ (3/28/2010)
GilaMonster (3/28/2010)
SwePeso (3/28/2010)
All records do have a "hidden" record identifier, when there is not primary key on the table.
I assume you mean 'clustered index', not 'primary key'.
The RID's there on all tables, cluster and heap.
I assume you mean 'row locator', not 'RID' 😀

No, I meant RID. The binary combination of File ID, Page No and Slot index is generally referred to as the Record ID, Row ID or RID.

http://msdn.microsoft.com/en-us/library/ms177484.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Garadin One Orange Chip Points: 29613 More actions · Answer 3

Thanks for the links. I actually did find both of those in my searching the other day, and part of the reason I thought it might exist in some undocumented column is due to Grant's post on %%lockres%% the other day. I was just looking for something that was like specifically %%rowid%% or something =).

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

Once again, it depends! 😛

From BOL on Nonclustered Index Structures:

If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier. This four-byte value is not visible to users.

The emphasis in italics is mine... and I love the word "uniqueifier".

[url= http://msdn.microsoft.com/en-us/library/ms177484.aspx%5D

http://msdn.microsoft.com/en-us/library/ms177484.aspx%5B/url%5D

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Paul White SSC Guru Points: 150468 More actions · Answer 5

GilaMonster (3/28/2010)
No, I meant RID. The binary combination of File ID, Page No and Slot index is generally referred to as the Record ID, Row ID or RID.http://msdn.microsoft.com/en-us/library/ms177484.aspx

Not the best link in the world to backup your point - it only mentions RID in connection with heaps. Not surprising, since it is about non-clustered indexes, which only contain a FileID:PageID:SlotNumber formatted row locator when built against a heap.

I much prefer the generic term 'row locator' - it seems more descriptive to me, and avoids ambiguity.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 6

The Dixie Flatline (3/28/2010)
I love the word "uniqueifier".

Me too 🙂

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Paul White NZ (3/28/2010)
GilaMonster (3/28/2010)
No, I meant RID. The binary combination of File ID, Page No and Slot index is generally referred to as the Record ID, Row ID or RID.http://msdn.microsoft.com/en-us/library/ms177484.aspx
Not the best link in the world to backup your point - it only mentions RID in connection with heaps. Not surprising, since it is about non-clustered indexes, which only contain a FileID:PageID:SlotNumber formatted row locator when built against a heap.

It's the only place I found that the RID is mentioned in BoL, since the physloc feature is undocumented. I could have linked to Paul Randal's blog, or Kalen's, but I thought BoL would be the best.

I much prefer the generic term 'row locator' - it seems more descriptive to me, and avoids ambiguity.

As you wish.

However

If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row.

Row locator is the RID only on a heap.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150468 More actions · Answer 8

GilaMonster (3/29/2010)
I could have linked to Paul Randal's blog

You already did. It had Row Locator in the title. :laugh:

GilaMonster (3/29/2010)
The row locator is technically (refer to what Bob posted) the RID on a heap and the cluster (plus uniqueifier if necessary) on a cluster

So now you know why I picked you up on the RID thing in the first place.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Paul White NZ (3/29/2010)
GilaMonster (3/29/2010)
The row locator is technically (refer to what Bob posted) the RID on a heap and the cluster (plus uniqueifier if necessary) on a cluster
So now you know why I picked you up on the RID thing in the first place.

No I don't.

The function that Paul Randal mentioned returns the RID (file ID, Page Number, Slot Index) regardless of whether the table that you're querying has a clustered index or not. Hence is is incorrect to say that it returns the row locator, because it does not when the table is a clustered index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150468 More actions · Answer 10

Gail,

I prefer to call it a physical row locator and it seems that the designers of that function do too. In fact, most modern texts seem to have moved to that sort of nomenclature, leaving RIDs to apply exclusively to heaps.

So, every row in every table does have something that identifies it uniquely - the row locator. That is what SQL Server uses to locate a row. The form of it varies depending on whether the object is a heap or b-tree. Every row in every table is also capable of being uniquely identified by a combination of FileID, PageID, and SlotNumber. You may call that combination an RID, by analogy with a heap, but that strikes me as imprecise.

The fact that the undocumented functions always return FileID, PageID, and SlotNumber simply means that the designers wanted its output to be the same for heaps and b-trees.

You may think I am just being picky - and initially I was, in a humourous way, since you did the same to Peso. I did include a smiley on that post, but it seemed to go unnoticed 🙁

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

nitinkumar_tss Old Hand Points: 331 More actions · Answer 11

Yes, I want the binary 8 byte combination. But I am not getting how will get that.

Paul White SSC Guru Points: 150468 More actions · Answer 12

nitinkumar_tss (3/29/2010)
Yes, I want the binary 8 byte combination. But I am not getting how will get that.

Why do you need it?

The thing we are discussing is for debugging use only, and is not supported for use in production systems.

edit: *and* it isn't available in SQL Server 2005 anyway.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 13

Again from BOL:

If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The whole pointer is known as a Row ID (RID).
If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier. This four-byte value is not visible to users. It is only added when required to make the clustered key unique for use in nonclustered indexes. SQL Server retrieves the data row by searching the clustered index using the clustered index key stored in the leaf row of the nonclustered index.

As I read the quote above, the row locator (which I loosely define as the pointer from the nonclustered index to the table itself) may be any of the following, depending on the nature of the table itself:

1. A RID, if the base table is a heap.

2. The clustered index key, if the clustered index is unique.

3. The clustered index key PLUS the uniqueifier, if the clustered index is not unique.

Is there any confusion about this, or are we simply clarifying terminology with the mutual respect and consideration that intelligent and well-educated minds are due? 😉

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Paul White SSC Guru Points: 150468 More actions · Answer 14

nitinkumar_tss (3/29/2010)
Yes, I want the binary 8 byte combination. But I am not getting how will get that.

Just waiting to hear the reason for the question. There is almost certainly a better way.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 15

The Dixie Flatline (3/28/2010)

Once again, it depends! 😛

From BOL on Nonclustered Index Structures:

If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier. This four-byte value is not visible to users.

The emphasis in italics is mine... and I love the word "uniqueifier".

[url= http://msdn.microsoft.com/en-us/library/ms177484.aspx%5D

http://msdn.microsoft.com/en-us/library/ms177484.aspx%5B/url%5D%5B/quote%5D

Hmm... I thought that was just...two bricks?:-D

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?