SQL Server Performance

Covered Index vs. many indexes on single columns

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by shekihan, Nov 21, 2008.

  1. shekihan New Member

    I have the following multi-join query:SELECT …FROM Table1 t1
    JOIN Table2 t1 on t1.col1 = t2.col1
    JOIN Table3 t1 on t1.col2 = t3.col2
    JOIN Table4 t1 on t1.col3 = t4.col3

    I want to performance tune this query.
    Which of the below two approaches would work better in this case:
    1. Create six single-column indexes on :
    t1.col1; t2.col1;
    t1.col2; t3.col2;
    t1.col3; t4.col3
    2. Create one covered index on t1.col1+t1.col2+t1.col3Are there any advantages of one approach over another in general case?
  2. MohammedU New Member

  3. shekihan New Member

    Thanks Mohammed!
  4. RedDevils New Member

    Before sugestion any idea Can you tell us in SELECT clause which columns you are considering (Table1 / Table2 / Table3 / Table4).
    -Abhijit, MCP
  5. shekihan New Member

    Thank you for your response Abhijit. I was trying to get the generic answer first. However I realize that generic aswer likely is not available. So, if you could show me some scenarious where one way is better than another one it would be what I am looking for. Ususally the column list in select includes some columns from each joined table.
    Thanks.
  6. moh_hassan20 New Member

    use index tunning advisor in SSMS

Share This Page