SQL Server Index Myth 4: Any Column Can Be Filtered in Multicolumn Indexes

The next common myth with indexes is that regardless of the position of the column in an index, the index can be used to filter for the column. As with the other myths discussed so far in this chapter, this one is also incorrect. An index does not need to use all the columns in a table. It does, however, need to start with the leftmost column in an index and use the columns from left to right, in order. This is why the order of the columns in an index is so important.

To demonstrate this myth, I’ll run through a few examples, shown in Listing 9. In the script, a table is created based on Sales.SalesOrderHeader with a primary key on SalesOrderID. To test the myth of searching all columns through multicolumn indexes, an index with the columns OrderDate, DueDate, and ShipDate is created.

Listing 9. Multicolumn Index Myth

USE AdventureWorks2014
GO
IF OBJECT_ID('dbo.MythFour') IS NOT NULL
 DROP TABLE dbo.MythFour
GO

SELECT SalesOrderID, OrderDate, DueDate, ShipDate
INTO dbo.MythFour
FROM Sales.SalesOrderHeader;
GO

ALTER TABLE dbo.MythFour
ADD CONSTRAINT PK_MythFour PRIMARY KEY CLUSTERED (SalesOrderID);
GO

CREATE NONCLUSTERED INDEX IX_MythFour ON dbo.MythFour (OrderDate, DueDate, ShipDate);
GO

 

 

With the test objects in place, the next thing to check is the behavior of the queries against the table that could potentially use the nonclustered index. First, I’ll run a query that uses the leftmost column in the index. Listing 8-10 gives the code for this. As shown in Figure 4, by filtering on the leftmost column, the query uses a seek operation on IX_MythFour.

Listing 10. Query Using Leftmost Column in Index

USE AdventureWorks2014

GO

SELECT OrderDate FROM dbo.MythFour
WHERE OrderDate = '2011-07-17 00:00:00.000'

Figure 4. Execution plan for leftmost column in index

Next you’ll look at what happens when querying from the other side of the index key columns. In Listing 8-11, the query filters the results on the rightmost column of the index. The execution plan for this query, shown in Figure 8-5, uses a scan operation on IX_MythFour. Instead of being able to go directly to the records that match the OrderDate, the query needs to check all records to determine which match the filter. While the index is used, it isn’t able to actually filter the rows.

Listing 11. Query Using Rightmost Column in Index

USE AdventureWorks2014
GO

SELECT ShipDate FROM dbo.MythFour
WHERE ShipDate = '2011-07-17 00:00:00.000'

Figure 5. Execution plan for rightmost column in index

At this point, you’ve seen that the leftmost column can be used for filtering and that filtering on the rightmost column can use the index but cannot use it optimally with a seek operation. The last validation is to check the behavior of columns in an index that are not on the left or right side of the index. In Listing 12, a query is included that uses the middle column in the index IX_MythFour. As with any execution plan, the execution plan for the middle column query, shown in Figure 6, uses the index but also uses a scan operation. The query is able to use the index but not in an optimal fashion.

Listing 12. Query Using Middle Column in Index

USE AdventureWorks2014
GO

SELECT DueDate FROM dbo.MythFour
WHERE DueDate = '2011-07-17 00:00:00.000'

Figure 6. Execution plan for middle column in index
The myth of how columns in a multicolumn index can be used is one that can sometimes be confusing. As the examples showed, queries can use the index regardless of which columns of the index are being filtered. The key is to effectively use the index. To accomplish this goal, filtering must start on the leftmost column of the index.

For all your application development needs, visit www.verbat.com for a fiscally conscious proposal that meets your needs ( So I can keep this blog going as well!!!!)

Alternatively click through the link   if you found this article interesting. (This will help the companies Search engine rankings)