CODEWITHSUNDEEP
sql-serverperformancet-sqlindexingsql-tuning
Hunter Nelson
Hunter Nelson

Reputation: 1985

Creating a clustered index on a foreign key is frequently joined to another table

I was recently doing some performance optimization/query tuning on a table and had a question about using a foreign key as a clustered index. The table structure/relationships is as follows:

I am working in an invoicing application and there are guidelines that can be defined on the invoice and the line items of the invoice for what the maximum allowed amount is to be submitted.

There is a parent table that stores only the conditions of which a guideline is applied such as the state the invoice was created, the zip, or the line item type. GuidelineCondition

There are two child tables that define only the monetary limits that are able to be submitted. GuidelineInvoiceAllowable, GuidelineLineItemAllowable.

These two child tables are accessed pretty much exclusively with a join to the parent condition table. Both of the child tables had clustered index on a synethetic non-meaningful key. I swapped the clustered index to the foreign key of the GuideLineCondition table, GuidelineConditionID. The clustered index of the parent table is the synthetic key/primary key GuidelineConditionID This allowed the optimizer to efficiently conduct a merge join on these tables since both tables in the join have ordered clustered indexes on the same joining column now.

Making the clustered index a foreign key like this violates some best practices of choosing a clustered index, but due to the access patterns of the table it seemed like it was the right call.

See this post for some best practices I'm thinking of. SQL Server - When to use Clustered vs non-Clustered Index?

Can a database expert comment on whether I made the right decision?

Upvotes: 3

Views: 770

Answers (1)

SMor
SMor

Reputation: 2862

Those are guidelines, not absolutes. The short answer is that there is no one-size-fits-all approach. To know with certainty that your clustered index is effective you need to test. And yes - a setup like yours where you have a parent/detail relationship and the detail is usually accessed via the parent (directly or indirectly) is a situation that is often appropriate for clustering on the pk of the parent. I will take this a step further and suggest that the pk of the detail table should include the parent table pk value(s) - meaning that it will consist of at least 2 columns.

And again - the only way to know if your solution works is to try it and test. You've done that.

Upvotes: 2

Related Questions