|
|
Imagine that we have to index properly following query based on JOIN between
Invoices and Customers tables. The query retrieves list of customers
that purchased something from Sales Person number 11. Result set is ordered by
Invoice date:
SELECT b.CustomerID, b.CustomerName, b.City,
a.DocDate AS PurchaseDate
FROM dbo.Invoice AS a
INNER JOIN dbo.Customers AS b
ON a.CustomerID = b.CustomerID
WHERE a.SlpCode = 11
ORDER BY a.DocDate;
|
Select query text and chose "Include Actual Execution Plan" option.
The results will look like this:
| CustomerID | CustomerName | City | PurchaseDate |
| 1 | Angelina Alba | santiago | 1999-01-14 |
| 4 | Pestiana Oliviera | saragosa | 1999-01-22 |
| 11 | Somerset Dogan | wellington | 1999-07-01 |
Query Execution Plan will look like this:
The law says to add indexes on CustomerID columns in both tables cause we use
them in JOIN condition:
CREATE UNIQUE CLUSTERED INDEX [idx1_customerid_clust]
ON [dbo].[Customers]
([CustomerID] ASC)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY];
CREATE NONCLUSTERED INDEX [idx1_customerid]
ON [dbo].[Invoice]
([CustomerID] ASC)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY];
EXEC sp_updatestats;
|
After adding two indexes query Execution Plan will look like this:
From this plan we can learn that "Index Seek" operator was used to JOIN
two data sets by CustomerID. On the other hand "Table Scan" was used
for identifying records with SlpCode = 11 and after it "Sort" operation
to order result set by DocDate.
So we can try to create composite index that will include SlpCode and DocDate
info. SlpCode will be first column, cause it is located in the
WHERE clause of the query.
CREATE NONCLUSTERED INDEX
[idx2_slpcode_docdate]
ON [dbo].[Invoice]
(
[SlpCode] ASC,
[DocDate] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY];
GO
EXEC sp_updatestats;
|
The finall Execution Plan will look like this:
|
