1

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)
2
  • Are you missing some filters in those queries or do you want to join from every row in shipping to all the details for it? It seems a bit strange to do that in a stored procedure - it sounds more like a report you might run once a month (and even then you'd have some sort of filter e.g just orders for customer X). If you have no filters, and you'd realistically need to visit every row of these tables, table scans and hash joins are almost always the way forward. Aug 10 at 21:26
  • @AndrewSayer Good intuition catching this! The join actually includes about 5 other tables, and those tables are what the report is filtered on (which I've already created indexes for) I didn't add the entirety of the join because I didn't want the question to be focused on the actual query, but on the affects of creating multiple indexes VS a single index Aug 11 at 16:30
2

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.

1
  • Thanks for the detailed response, I was slightly concerned about space issues since this is an old server and not a whole lot has been done to 'clean' it out. I'm glad my intuition proved to be right, and I'll keep that rule in mind going forward! Aug 10 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.