SDU Tools: ListNonIndexedForeignKeys to Avoid a Code Smell
I’ve written before about how important it is to find code smells.
One of these relates to foreign keys. I’ve argued in previous posts about the importance of foreign keys but if you do have them in place, you need to index them.
When SQL Server creates a primary key, it creates an index under the covers to support the primary key. The index has the same name as the key. (And is one of the reasons why you should name your primary keys and not let the system do it for you). A primary key needs to be both unique and NOT NULL. So SQL Server creates an index so it can quickly check if a value already exists.
So if I have a Sales.Customers table with a CustomerID as the primary key, there will be an index on the CustomerID column.
SQL Server also creates an index to support unique constraints. Again, this is because it needs to be able to check whether the value already exists.
Foreign keys are where the problems can occur because SQL Server does not automatically create an index for these. As an example, if I have a Sales.Orders table and it has a CustomerID column declared as a foreign key, when I’m inserting or updating an order, I don’t need an index on the CustomerID column in the Sales.Orders table. I’m using the primary key index on CustomerID in the Sales.Customers table to perform the lookup.
The problem with this logic is:
- What is the chance that you’ll want to find all the orders for a particular customer? (pretty high)
- If you want to delete a customer, what happens?
For the first issue, you certainly want to have an index. The problem is that an index on just the CustomerID column might well not be a great index for that query. It’s likely you want other key columns or included columns in the index. For example, you might want the date of the order.
For the second issue of deletes though, things can get very nasty. Deleting a single row from Sales.Customers requires a full scan of the entire Sales.Orders table to make sure the customer doesn’t have any orders.
Ouch!
I really wish that SQL Server automatically indexed foreign keys unless you used some sort of I_KNOW_WHAT_IM_DOING option to turn it off ie: tell SQL Server that you’ll deal with it.
A huge number of performance issues would be solved by just doing that.
So where does the tool fit it?
One of the code smells that we go looking for, is declared foreign keys where the foreign key columns aren’t the left-most columns of at least one non-clustered index.
(I know that might take a moment to sink in).
As an example, with our table from before, if in the Sales.Orders table I declare CustomerID as a foreign key to the Sales.Customers.CustomerID column, I’d better find at least one nonclustered index that has CustomerID as the first column. I don’t care if the index is on CustomerID and OrderDate, as long as CustomerID is the first key in the index.
One of our SDU Tools is ListNonIndexedForeignKeys. It’s a stored procedure that takes 3 parameters. The first parameter is the database name. The second parameter is a list of schemas or the word ALL (which is the default). The third parameter is a list of tables or the word ALL (which again in the default).
So to just check a whole database, you can just execute:
This example checked the whole WideWorldImporters database. When I was designing that database, I made a conscience decision to not index the LastEditedBy column. You can see that the tool flags that.
You can see the tool in action here:
For more information on our free SDU Tools, look here:
http://sqldownunder.com/sdu-tools
2017-12-20