Echoes from the field 1: Should SQL Server automatically index foreign key constraints?

We spend a lot of time doing performance tuning work for clients. Inappropriate indexing strategy is often high on the list of issues that we identify. Most of these issues can’t be fixed by SQL Server itself and require a detailed understanding of both how the tables are structured and how they are accessed.

However, there is one very common problem however that I believe SQL Server could assist with.

Look at the primary key constraint on the table below:

CREATE TABLE Sales.Customers
(
	CustomerID INT IDENTITY(1,1)
	  CONSTRAINT PK_Sales_Customers PRIMARY KEY,
	TradingName varchar(40) NOT NULL,
	DeliveryLocation GEOGRAPHY NULL
);

When you assign that primary key constraint, SQL Server must ensure that the values in the column are both UNIQUE and NOT NULL. To help it do this, it automatically creates an index. If there isn’t a clustered index on the table, it will also default to making this a clustered index.

We could have avoided this by specifying NONCLUSTERED when defining the primary key if we wanted to cluster the table some other way or if we wanted to keep the table as a heap (with no clustered index). We can discuss the pros and cons of that another day. You can see the index created by querying the sys.indexes system view:

SELECT * FROM sys.indexes
	WHERE object_id = OBJECT_ID('Sales.Customers','TABLE');

Amongst other columns, this command returns:

Note the type is shown as CLUSTERED even though we did not specify that.

Now let’s create a table that refers to our Sales.Customers table:

CREATE TABLE Sales.Orders
(
	OrderID INT IDENTITY(1,1)
	  CONSTRAINT PK_Sales_Orders PRIMARY KEY,
	CustomerID INT NOT NULL
	  CONSTRAINT FK_Sales_Orders_Customers
	  FOREIGN KEY REFERENCES Sales.Customers(CustomerID),
	OrderDate DATE NOT NULL,
	ShippingCost DECIMAL(18,2) NOT NULL
);

If we again query the sys.indexes system view by executing:

SELECT * FROM sys.indexes
	WHERE object_id = OBJECT_ID('Sales.Orders','TABLE');

You will see the command returns similar values to before:

No index has been created to support the foreign key constraint.

That makes sense because the only index we need to support the foreign key constraint is the primary key on the Sales.Customers table. We need to be able to quickly find out if the customer already exists when inserting an order.

But is that practical?

Would an index on the foreign key column in the Sales.Orders table be useful?

Ask yourself what the likelihood is of needing to find all the orders for a given customer. It’s very, very likely. This is such a common cause of performance problems. In most situations, we need to traverse back across the foreign key relationship, not just follow it in one direction.

You may be wondering then why SQL Server doesn’t just create an index by default. There are two reasons.

The first is that you may have an application that doesn’t need to ever traverse the relationship in the reverse direction. In that case, you would have just slowed down the system during updates and taken up disk space and added to your database maintenance plans for no reason.

The second reason is that you can often build a better index than a default index on a foreign key would provide. For example, rather than just locating all the orders for a given customer, you might commonly need to locate all the orders for a given customer within a date range. In that case, you would be better off with an index such as:

CREATE INDEX IX_Sales_Orders_CustomerLookup
  ON Sales.Orders (CustomerID, OrderDate);

But perhaps the query also needs to retrieve the ShippingCost column from that table. If so, the following index would be preferable:

CREATE INDEX IX_Sales_Orders_CustomerLookup
  ON Sales.Orders (CustomerID, OrderDate)
  INCLUDE(ShippingCost);

It is quite common that you would be able to create better indexes on columns involved in foreign key columns than a default index would provide. However, we mentioned that a really common problem is that none at all are often created.

One code smell I often go looking for is declared foreign keys where the foreign key columns are not the first components of at least one non-clustered index.

Deletes can be painful

Where this is really painful is in delete operations.

Imagine you have 8 million orders and that table has a foreign key to the customer table. If there’s no index on the CustomerID column in the Orders table, what happens when you try to delete a customer?

SQL Server is going to have to read the entire Orders table before it can let you delete that one customer. That’s insanely painful.

A better default

In general, I believe that SQL Server would be better creating an index on foreign key columns unless you specify an option that basically says Don’t create a default index as I know what I’m doing. Perhaps we could have syntax something like:

CREATE TABLE Sales.Orders
(
	OrderID INT IDENTITY(1,1)
	  CONSTRAINT PK_Sales_Orders PRIMARY KEY,
	CustomerID INT NOT NULL
	  CONSTRAINT FK_Sales_Orders_Customers
	  NONINDEXED FOREIGN KEY 
	  REFERENCES Sales.Customers(CustomerID),
	OrderDate DATE NOT NULL,
	CreditLimit DECIMAL(18,2) NOT NULL
);