RID scan

The Positioning by Row IDentifier (RID) scan is found only in query plans that use the second or strategy that the optimizer can choose, the general or strategy. The general or strategy may be used when multiple or clauses are present on different columns. An example of a query for which the optimizer can choose a general or strategy and its showplan output is:

use pubs2
go
set showplan on
go
select id from sysobjects where id = 4 or name = 'foo'

QUERY PLAN FOR STATEMENT 1 (at line 1).

6 operator(s) under root

The type of query is SELECT.

ROOT:EMIT Operator (VA = 6)

    |RID JOIN Operator (VA = 5)
    | Using Worktable2 for internal storage.
    |
    |   |HASH UNION Operator has 2 children.
    |   | Using Worktable1 for internal storage.
    |   |
    |   |   |SCAN Operator (VA = 0)
    |   |   |  FROM TABLE
    |   |   |  sysobjects
    |   |   |  Using Clustered Index.
    |   |   |  Index : csysobjects
    |   |   |  Forward Scan.
    |   |   |  Positioning by key.
    |   |   |  Index contains all needed columns. Base table will not be read.
    |   |   |  Keys are:
    |   |   |    id ASC
    |   |   |  Using I/O Size 2 Kbytes for index leaf pages.
    |   |   |  With LRU Buffer Replacement Strategy for index leaf pages.
    |   |
    |   |   |SCAN Operator (VA = 1)
    |   |   |  FROM TABLE
    |   |   |  sysobjects
    |   |   |  Index : ncsysobjects
    |   |   |  Forward Scan.
    |   |   |  Positioning by key.
    |   |   |  Index contains all needed columns. Base table will not be read.
    |   |   |  Keys are:
    |   |   |    name ASC
    |   |   |  Using I/O Size 2 Kbytes for index leaf pages.
    |   |   |  With LRU Buffer Replacement Strategy for index leaf pages.
    |
    |   |RESTRICT Operator (VA = 4)(0)(0)(0)(11)(0)
    |   |
    |   |   |SCAN Operator(VA = 3)
    |   |   |  FROM TABLE
    |   |   |  sysobjects
    |   |   |  Using Dynamic Index.
    |   |   |  Forward Scan.
    |   |   |  Positioning by Row IDentifier (RID).
    |   |   |  Using I/O Size 2 Kbytes for data pages.
    |   |   |  With LRU Buffer Replacement Strategy for data pages.

In this example, the where clause contains two disjunctions, each on a different column (id and name). There are indexes on each of these columns (csysobjects and ncsysobjects), so the optimizer chose a query plan that uses an index scan to find all rows whose id column is 4 and another index scan to find all rows whose name is “foo.”

Since it is possible that a single row has both an ID of 4 and a name of “foo,” that row would appear twice in the result set. To eliminate these duplicate rows, the index scans return only the row identifiers (RIDs) of the qualifying rows. The two streams of RIDs are concatenated by the HASH UNION operator, which also removes any duplicate RIDs.

]The stream of unique RIDs is passed to the RID JOIN operator. The rid join operator creates a worktable and fills it with a single-column row with each RID. The RID JOIN operator then passes its worktable of RIDs to the RID SCAN operator. The RID SCAN operator passes the worktable to the access layer, where it is treated as a keyless nonclustered index and the rows corresponding to the RIDs are fetched and returned.

The last SCAN in the showplan output is the RID SCAN. As can be seen from the example output, the RID SCAN output contains many of the messages already discussed above, but it also contains two messages that are printed only for the RID SCAN:

Using Dynamic Index – indicates the SCAN is using the worktable with RIDs that was built during execution by the RID JOIN operator as an index to locate the matching rows.
Positioning by Row Identifier (RID) – indicates the rows are being located directly by the RID.