March 26, 2010 at 11:59 am
I want to get the rowid internally generated at table level in sql server. Because I need to perform some task according to the rowid.
March 26, 2010 at 12:22 pm
do you want to return a result with a row number as part of the output or do you want to grab a specific row, as in you want row 10 of the data set?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
March 26, 2010 at 12:24 pm
nitinkumar_tss (3/26/2010)
I want to get the rowid internally generated at table level in sql server. Because I need to perform some task according to the rowid.
Unfortunately, there isn't an internally generated rowid at the table level in SQL Server.
March 26, 2010 at 1:52 pm
It seems like Oracle uses something like this that you can access, but unfortunately MS SQL does not. If you can give more details on your goal, it's possible we can suggest another way to do it.
March 27, 2010 at 8:25 am
what kind of row id du you need.
If you need row id which is stored in your table then you can use table's identity value for the same
March 27, 2010 at 1:14 pm
there is not rowid equivalent in SQL Server
March 28, 2010 at 2:00 am
Lynn Pettis (3/26/2010)
nitinkumar_tss (3/26/2010)
I want to get the rowid internally generated at table level in sql server. Because I need to perform some task according to the rowid.Unfortunately, there isn't an internally generated rowid at the table level in SQL Server.
Well, technically there is, sort of, but it is undocumented and not intended for use by end users.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 28, 2010 at 6:22 am
Paul White NZ (3/28/2010)
Well, technically there is, sort of, but it is undocumented and not intended for use by end users.
+1 for that.
All records do have a "hidden" record identifier, when there is not primary key on the table. That's the way SQL Server distinguishes between two identical records.
Read more about what Kalen Delaney writes about this.
N 56°04'39.16"
E 12°55'05.25"
March 28, 2010 at 8:47 am
SwePeso (3/28/2010)
Paul White NZ (3/28/2010)
Well, technically there is, sort of, but it is undocumented and not intended for use by end users.
+1 for that.
All records do have a "hidden" record identifier, when there is not primary key on the table. That's the way SQL Server distinguishes between two identical records.
Read more about what Kalen Delaney writes about this.
Is it actually a row identifier, like this is row 10? Reason I ask is that is what most people are looking for when they are asking about an internal rowid. Is that what this individual is asking, not sure.
March 28, 2010 at 9:53 am
Lynn Pettis (3/28/2010)
Is it actually a row identifier, like this is row 10? Reason I ask is that is what most people are looking for when they are asking about an internal rowid. Is that what this individual is asking, not sure.
Think they're talking about the RID. Row Identifier, binary value, 8-byte combination of file id, page number and slot index. It's accessible on all tables (not just heaps), however it's likely to change whenever the clustered index is rebuilt (and, on SQL 2008, if the heap is ever rebuilt) because it's the physical location of the row.
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
March 28, 2010 at 9:55 am
SwePeso (3/28/2010)
All records do have a "hidden" record identifier, when there is not primary key on the table. That's the way SQL Server distinguishes between two identical records.
I assume you mean 'clustered index', not 'primary key'.
The RID's there on all tables, cluster and heap. It's only used in nonclustered indexes if the underlying table's a heap, but it can be viewed on all tables.
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
March 28, 2010 at 9:59 am
Paul White NZ (3/28/2010)
Lynn Pettis (3/26/2010)
nitinkumar_tss (3/26/2010)
I want to get the rowid internally generated at table level in sql server. Because I need to perform some task according to the rowid.Unfortunately, there isn't an internally generated rowid at the table level in SQL Server.
Well, technically there is, sort of, but it is undocumented and not intended for use by end users.
My gut was telling me that was the case, but my google skills apparently failed me. I couldn't find anything on it. Anyone have a link?
March 28, 2010 at 10:01 am
GilaMonster (3/28/2010)
I assume you mean 'clustered index', not 'primary key'.
The RID's there on all tables, cluster and heap. It's only used in nonclustered indexes if the underlying table's a heap, but it can be viewed on all tables.
Yes, of course. You're right. I meant clustered index.
N 56°04'39.16"
E 12°55'05.25"
March 28, 2010 at 10:07 am
Garadin (3/28/2010)
My gut was telling me that was the case, but my google skills apparently failed me. I couldn't find anything on it. Anyone have a link?
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
March 28, 2010 at 10:21 am
GilaMonster (3/28/2010)
Garadin (3/28/2010)
My gut was telling me that was the case, but my google skills apparently failed me. I couldn't find anything on it. Anyone have a link?
%%LockRes%% in 2005 is the closest equivalent.
I wasn't going to mention it though, since I thought it would just confuse matters.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply