RID Lookups

Question

RID Lookups

smallmoney

SSCarpal Tunnel

Points: 4378
More actions
January 3, 2011 at 3:52 pm

#231645

Over the holidays I picked up a book by Grant Fritchey.
This might be a dumb question or I maybe confused.
Lets say a table has a nonclustered index. A query runs and a nonclustered index seek and rid lookup happens.
What happens if there is no unqiue id, how would the rid lookup know what rows to match to return the noncovered columns? Or would an RID not happen?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

SQL Iron Chef (1/3/2011)
Over the holidays I picked up a book by Grant Fritchey.
This might be a dumb question or I maybe confused.
Lets say a table has a nonclustered index. A query runs and a nonclustered index seek and rid lookup happens.
What happens if there is no unqiue id, how would the rid lookup know what rows to match to return the noncovered columns? Or would an RID not happen?
Thanks

A RID is an internal value that you can't see that applies a unique value to a clustered index, or every row in a heap if one doesn't exist. There is always a way to find each unique record at the table level, but if it's a RID, it's not directly accessible to you.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

Yeah, Craig nailed it. The RID is created in order to give the heap table a method of finding records.

There is a way to see the value of the RID, %%lockres%%, but I wouldn't recommend using it for anything other than investigation. Here's another mechanism for tracking down similar information.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ramukar LivingForSQLServer SSCommitted Points: 1749 More actions · Answer 3

SQL Iron Chef,

Here is simple excercise to test this scenario.

create a table name tEmployee,

create table tEmployee(

intEmpCode int,

strName varchar(100),

strCity varchar(100))

create NCI on intEmpCode,

CREATE NONCLUSTERED INDEX NCI_tEmployee_intEmpCode ON tEmployee(intEmpCode)

Insert 100 dummy records (all will 1001 as intEmpCode)

insert into tEmployee values(1001, 'AAAA','CHENNAI')

GO 100

here is the DBCC commands to list all pages allocated for tEmployee

DBCC TRACEON(3604)

DBCC IND('LearningInternals', 'tEmployee', -1)

DBCC TRACEOFF(3604)

GO

find non clustered index page number PagePID (where IndexId = 2 and PageType = 2 and IndexLevel = 0)

In my case index page number is 154,

here is the command to list all index entries in index page 154.

-- LearningInternals is the db name, 1 is the file-id (PRIMARY), 154 is the page number and 3 is the printing option.

DBCC TRACEON(3604)

DBCC PAGE('LearningInternals', 1, 154, 3)

DBCC TRACEOFF(3604)

if you see the output, you may notice that

1. all key values of NCI are 1001

2. but HEAP RID values are different

for example

0x9800000001000000

0x9800000001000100

0x9800000001000200

0x9800000001000300

0x9800000001000400

0x9800000001000500

0x9800000001000600

meaning duplicates records are there, index keys (both Clustered index and NonClustered Index) will add its own unique values along with index keys to uniquely find data record.

hope this helps.

smallmoney SSCarpal Tunnel Points: 4378 More actions · Answer 4

Hey guys thanks for clarifying this, especially craig and ramkumar.

I didn't expect the author of the book to find my post and reply---that was cool. 😀