Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Use of Indexes

  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Unanswered: Use of Indexes

    If I am joining 2 tables on a column and use another column in the where clause, will the index need be created on both the columns?

    eg:

    SELECT tbl1.Col1
    FROM tbl1
    INNER JOIN tbl2
    ON tbl1.col2 = tbl2.col1
    WHERE tbl1.col3 = @Var1

    In the above case for tbl1 will the index have to be on col2 and col3 or just col3?

    Also, is there is a document somewhere on the net that explains the execution plan (graphical) ?

    Thanks for your time

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Also, is there is a document somewhere on the net that explains the execution plan (graphical) ?

    I would say ...

    Refer to the holy book
    [Edit]
    SQL Server Books Online
    [/Edit]
    and you shall find the answer
    Last edited by Enigma; 11-18-03 at 13:43.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Use of Indexes

    I think you should put both indexes...

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Are you already using a clustered index ?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Enigma
    I would say ...
    Very helpful

    Index on Table 1 Col3,Col2,Col1...you'll get an index seek, bu adding col1 to the index, it won't have to go to the data page...

    And and index on table fopr Col2...

    MOO (my own opinion)

    Hey but don't take my word...do a show plan and see it...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Brett ...

    I think that the basic concepts should be clear. That is why I asked him to refer to the BOL .
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well, that's more helpful

    quote:
    --------------------------------------------------------------------------------

    Refer to the holy book
    [Edit]
    SQL Server Books Online
    [/Edit]
    and you shall find the answer


    But what you won't find in there is the overloading the index with a non predicate column...this allows sql to just use the index, and not have to make another trip to the data page for every row that satisfies the result...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Thanks for the info Brett .. didnt know that ... is that your experience speaking or is that documented somewhere.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did that from DB2 days...don'y know if it's documented, but it's an easy concept to grab...the less work you make for sql server the faster it'ss be...so if all it needs to use is the index, the faster the select.

    Trade offs, always trade offs though..means more overhead on INSERTS and updates..
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, it's documented, but SQL server refers to it as a covered index, rather than an overloaded index.

    blindman

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    Brett-

    Thanks for the information. Yes, it is called covered index. I got it.
    Anyway, I will go to BOL to read more about indexes. thanks for the information guys.

    - CB


    Originally posted by blindman
    Yeah, it's documented, but SQL server refers to it as a covered index, rather than an overloaded index.

    blindman

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah...found it in "Designing an Index"


    Covered queries can improve performance. Covered queries are queries where all the columns specified in the query are contained within the same index. For example, a query retrieving columns a and b from a table that has a composite index created on columns a, b, and c is considered covered. Creating indexes that cover a query can improve performance because all the data for the query is contained within the index itself; only the index pages, not the data pages, of the table must be referenced to retrieve the data, thereby reducing overall I/O. Although adding columns to an index to cover queries can improve performance, maintaining the extra columns in the index incurs update and storage costs.
    "the holy book (BOL)"

    EDIT: Good for the interview......Thanks
    Last edited by Brett Kaiser; 11-18-03 at 14:28.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Brett ... You got to respect "The Holy Book (BOL)" . Sometimes I feel I have read even 5% of it till now. . Is a printable version or printed version availible anywhere
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Enigma
    Brett ... You got to respect "The Holy Book (BOL)" . Sometimes I feel I have read even 5% of it till now. . Is a printable version or printed version availible anywhere
    Hey, does it sound like I'm dissing it?

    No way.

    It's open on my desktop 24x7

    BUT, there are a lot of great books out there...

    http://www.sqlteam.com/store.asp
    Last edited by Brett Kaiser; 11-18-03 at 15:19.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Peter Jackson has the movie rights, I believe. He has started filming on location in Redmond WA.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •