Posts tagged indexes

Last month I observed an interesting behavior of the query optimizer in SQL Server 2008 (this probably can be observed on the previous versions too). The example runs on the AdventureWorks2008 sample database. Consider the following query:

USE AdventureWorks2008;

GO

SELECT

  h.SalesOrderID,

  h.OrderDate,

  c.CustomerID,

  c.AccountNumber

FROM Sales.Customer AS c

INNER JOIN Sales.SalesOrderHeader AS h

ON c.CustomerID = h.CustomerID;

GO

If you look at the execution plan it looks like this:

Well, at first it may seem to be not very interesting. But if you look at the name of the nonclustered index scanned (IX_Customer_TerritoryID index from Sales.Customer table) you may start to wonder: why this individual index was used here? The index has the TerritoryID column as a key, and of course it also contains the CusomerID column since this is a clustered index key. Now, if you notice that the AccountNumber column is computed from CustomerID, then it’s easy to justify the optimizer’s choice (probably IX_Customer_TerritoryID is the smallest index that can be scanned to get all the necessary data from Sales.Customer table in this particular query). But wait! There is an index on the AccountNumber computed column! Why the hell it was not used in this case? Well, let’s try some hint here:

USE AdventureWorks2008;

GO

SET STATISTICS IO ON;

GO

SELECT

  h.SalesOrderID,

  h.OrderDate,

  c.CustomerID,

  c.AccountNumber

FROM Sales.Customer AS c

INNER JOIN Sales.SalesOrderHeader AS h

ON c.CustomerID = h.CustomerID;

GO

SELECT

  h.SalesOrderID,

  h.OrderDate,

  c.CustomerID,

  c.AccountNumber

FROM Sales.Customer AS c WITH (INDEX(AK_Customer_AccountNumber))

INNER JOIN Sales.SalesOrderHeader AS h

ON c.CustomerID = h.CustomerID;

GO

SET STATISTICS IO OFF;

GO

According to the I/O statistics the second query (the one with INDEX hint) reads more pages from the Sales.Customer table than the first query. The cost of both queries is 50% (see below).

Looks like a nice sample of the cost-based optimization, doesn’t it? So, even when your computed column is indexed, you can expect the computations to be made anyway in some cases.