Separate Specific Indexes VS Single Inclusive Index

All we need is an easy explanation of the problem, so here it is.

I have four tables, all of which have around the same large number of rows (except Customer):
Shipping (PackNumber, PackLine, OrderNumber, OrderLine, CustomerID)
Orders (OrderNumber, OrderLine)
Invoices (PackNumber, PackLine)
Customer (CustID)

I was running query plans on 2 stored procedures while working on optimization, one query has a join like this

SELECT ...
FROM Shipping s 
    INNER JOIN Orders o ON s.OrderNumber = o.OrderNumber AND s.OrderLine = o.OrderLine
    INNER JOIN Customer c ON s.CustomerID = c.CustomerID

And the other has a join like this

SELECT ...
FROM Shipping s
    INNER JOIN Invoices i ON s.PackNumber = i.PackNumber AND s.PackLine = i.PackLine
    INNER JOIN Customer c ON s.CustomerID = c.CustomerID

SQL First recommended creating these 2 indexes for the stored procedures respectively

CREATE INDEX ... ON Shipping (CustomerID) INCLUDE (OrderNumber, OrderLine)
CREATE INDEX ... ON Shipping (CustomerID) INCLUDE (PackNumber, PackLine)

Could someone please explain the pros/cons of creating two indexes vs just creating one?

CREATE INDEX ... ON Shipping (CustomerID) INCLUDE (OrderNumber, OrderLine, PackNumber, PackLine)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This is a case where creating two separate indexes is unlikely to be the right choice. (There’s a small corner-case that I’ll get to at the end)

With these two indexes:

CREATE INDEX ... ON Shipping (CustomerID) INCLUDE (OrderNumber, OrderLine)
CREATE INDEX ... ON Shipping (CustomerID) INCLUDE (PackNumber, PackLine)

Both indexes will provide the same ability for an index to seek on the key column (CustomerID). The INCLUDE columns are useful to make an index "covering" (ie, to prevent having to do lookups on another index (usually the clustered index, or heap) to get additional columns to satisfy the query). Note that combining the key columns doesn’t work the same way, since key columns affect ordering & the ability to do index seeks–however ordering for included columns has no such affect.

Combining indexes with identical key columns, and differing included columns is almost always the right call. I would definitely take those two indexes, and combine them into the single index you recommend.

CREATE INDEX ... ON Shipping (CustomerID) INCLUDE (OrderNumber, OrderLine, PackNumber, PackLine)

The one scenario where it sometimes makes sense to keep a narrower index is when you intentionally want to scan an index. When you are knowingly/intentionally scanning an index, you often want to keep it narrow. Narrow indexes are smaller–they take up less space on disk–which means your scans will take fewer IOs, and complete faster. Doubling the size on disk may double the time it takes to scan.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply