Post reply

RID Lookups

  • 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

  • January 3, 2011 at 4:11 pm

    #1268699

    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

  • January 3, 2011 at 8:52 pm

    #1268732

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • January 3, 2011 at 8:57 pm

    #1268734

    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.

  • January 5, 2011 at 8:26 am

    #1269549

    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. 😀

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

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