3

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?

Hunter Nelson
  • 1,707
  • 3
  • 20
  • 37
  • 1
    Since you asked for a comment =). It's a good question and I see your point in going against the best practices. I can do that path too ONLY IF: Those child tables are domain tables, almost never getting inserts/deletes; That join is hit like crazy; Has tested that solution using um actual production backup (real data) simulating real workload (acid test). Another approache I can recomend to explore: use a indexed view – jean Nov 09 '17 at 16:08
  • Data is rarely inserted and never deleted. The join is accessed regularly and it will be tested using production quality data in a couple of weeks and the data in our dev environment isn't half bad either. An indexed view would be a great solution but implementing it would be out of scope for the time allotted to solve the original problem. – Hunter Nelson Nov 09 '17 at 17:42

1 Answers1

2

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.

SMor
  • 2,830
  • 4
  • 11
  • 14
  • I now have a non clustered primary key which is a synthetic auto-number key on the detail tables and a clustered index on the foreign key of the parent table. Are you saying that the non clustered primary key should include the foreign key or that the clustered index on the foreign key should include the synthetic key? – Hunter Nelson Nov 09 '17 at 17:45
  • "synthetic auto-number" Can we just use the terminology of sql server? Presumably an identity column? Let's ignore clustering for the moment. Yes - the primary key of your detail table should contain the fk columns to the parent. Look at the adventureworks DB for the sales order tables as an example. – SMor Nov 14 '17 at 00:56
  • https://stackoverflow.com/questions/13225928/is-there-a-difference-between-surrogate-key-synthetic-key-and-artificial-key Synthetic key is real term, and I said synthetic auto number to indicate it was an auto numbering int and not a GUID, which may have impacted the answer to whether or not it would be acceptable to cluster on it as a foreign key.. – Hunter Nelson Nov 15 '17 at 14:23