Search code examples
indexingsql-server-2000

What's the general order of index for SQL Server clauses?

SELECT Col1
FROM   Tab1
INNER JOIN Tab2 ON Tab1.Col2 = Tab2.Col2
WHERE  Tab1.Col3 = "some ID"
GROUP  BY Col1, Col4
ORDER  BY Col4 DESC

What would be the general way to create a covering index for the above case?

Is it like the columns from

  1. ORDER BY
  2. GROUP BY
  3. WHERE
  4. JOIN
  5. SELECT

which is like

INDEX (Col4, Col1, Col3, Col2) ?

What I use is SQL Server 2000

Solution

  • This is what I found by testing

    • First should be the JOIN

    o Index field order should be the reverse of JOIN order [bottom up]

    o If JOIN ON PK, then any Index you create on the table starting the PK field will NOT be used. Server will only use the PK.

    • Then WHERE

    o Index field order should be the reverse of WHERE order [bottom up]

    • Then GROUP BY

    • Then ORDER BY

    • Then SELECT [if doing Covering Index]

    Also Index any computed columns

    Having partial indexes for a View/Sproc doesn’t speed up. If few columns indexed on a GROUP BY in a multi table query, the index slows it down. Originally it uses PK and it is fast. So don’t bother partial indexes on multi table queries

    If there is a SQL statement executed on a View, there is no performance improvement if you index the SQL Statement. The index should be based on the View

    Select * from Leasedetailsqry where status ='Active' And (territory LIKE 'Quebec') ORDER BY id

    Indexing Territory, Status is not giving any performance improvement. You should index based on the View Leasedetailsqry