O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

The Path of the Query

A good way to understand how to design efficient indexes is to observe and learn from the various possible paths' queries use to locate data using indexes.

The following section compares and contrasts ten different query paths. Not every query path is an efficient query path.

A good test table for observing the 10 query paths in the AdventureWorks2012 database is the Production.WorkOrder table. It has 72,591 rows, 10 columns, and a single-column clustered primary key. Here's the table definition:

CREATE TABLE [Production].[WorkOrder](
[WorkOrderID] [int] IDENTITY(1,1) NOT NULL,
[ProductID] [int] NOT NULL,
[OrderQty] [int] NOT NULL,
[StockedQty] AS (isnull([OrderQty]-[ScrappedQty],(0))),
[ScrappedQty] [smallint] NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NULL,
[DueDate] [datetime] NOT NULL,
[ScrapReasonID] [smallint] NULL,
[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_WorkOrder_WorkOrderID] PRIMARY KEY CLUSTERED 
 ([WorkOrderID] ASC) 
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
  ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];

The WorkOrder table has three indexes, each with one column as identified in the index name:

  • PK_WorkOrder_WorkOrderID (Clustered)
  • IX_WorkOrder_ProductID (Nonunique, Nonclustered)
  • IX_WorkOrder_ScrapReasonID (Nonunique, Nonclustered)

Performance data for each path, listed in Table 45.1, was captured by watching the T-SQL → SQL:StmtCompleted and Performance ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required