Post reply

Missing Lookup operator?

  • March 27, 2013 at 5:30 am

    #291395

    Hi All

    Consider the following table and their indexes

    create table test1 (Col1 int identity (1,1) primary key clustered, Col2 int, Col3 int, Col4 int)

    create nonclustered index ncx on test1 (Col4)

    create table test2 (Col1 int identity (1,1) primary key clustered, Col2 int, Col3 int, Col4 int)

    create nonclustered index ncx2 on test2 (Col3,Col4)

    Consider the following query

    select test2.Col2,test1.Col2 from test1

    inner join test2 on test1.Col1 = test2.Col1

    where test2.Col1 < 5

    The execution plan shows 2 Clustered Index seeks and a nested loops join.

    Because there are no indexes on the columns in my select list (test2.Col2,test1.Col2), shouldn't I be seeing lookups?

    Thanks

  • March 27, 2013 at 5:34 am

    #1601089

    Hover over each of those clustered index seek operators and examine the columns output. What would you want a lookup for?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • March 27, 2013 at 5:58 am

    #1601096

    ChrisM@Work (3/27/2013)

    Hover over each of those clustered index seek operators and examine the columns output. What would you want a lookup for?

    Thanks

    So because I'm joining test1.col1 = test2.col1 and they both have clustered indexes defined on them, when I want Col2 from each table, SQL just returns the corresponding Col2 value from each table, as it's already there.

    Should have done more testing.

    Thanks

  • March 27, 2013 at 6:56 am

    #1601123

    Just remember that the clustered index is both an index and the data. So if a query can use the index part of the cluster to satisfy the operations, the data comes along for free.

    "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

  • March 27, 2013 at 7:12 am

    #1601133

    Thanks

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

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