March 12, 2015 at 9:29 am
I am troubleshooting some slow queries and notice that the primary table involved has a strange setup.
This table stores detailed information about time cards. This important column is Week_ending(that is the CI)
It has one column( identity column) as the PK and...
It has another column is used as CI
Is there ever a benefit to this approach?
I would think combining these two rows into the CI would be best?
March 12, 2015 at 9:39 am
like many things, it depends.
Are there other indexes on the table? (besides the PK and CI)
How is the data inserted (in CI order?)
How wide are the CI index columns?
How is the data used (OLAP/DW, OLTP)?
Is the PK ever used in queries? What about Foreign Keys? Note using an Identity as a PK can keep the FK refs small (e.g. using an identity column defined as an integer).
Gerald Britton, Pluralsight courses
March 12, 2015 at 9:47 am
Thanks for your reply.. We don't use FK... 🙁
Are there other indexes on the table? (besides the PK and CI) - Yes
How is the data inserted (in CI order?) --- Yes
How wide are the CI index columns? --- Weekending(smalldatetime 4)
How is the data used (OLAP/DW, OLTP)? -- OLTP and reporting
Is the PK ever used in queries? What about Foreign Keys? Note using an Identity as a PK can keep the FK refs small (e.g. using an identity column defined as an integer).
March 12, 2015 at 10:16 am
TryingToLearn (3/12/2015)
It has one column( identity column) as the PK and...
It has another column is used as CI
Is there ever a benefit to this approach?
Yes; indeed, that is the only correct approach. The clustering key column(s) should always be what is best for that table and its overall usage. If the PK needs to be different column(s), it(those) should be defined separately.
I would think combining these two rows into the CI would be best?
Not at all really. The lead column(s) is(are) most important in an index. Thus, to insure the best use of indexes, each index should contain only the key(s) it needs, in the best order for processing.
Edit: Added /quote label to end of second quote.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 12, 2015 at 10:48 am
How unique is the data in your WeekEnding column? If it's not very selective, the statistics might be poor and you may not be seeing good use within the execution plans. Most of the queries use the WeekEnding for data retrieval?
Oh, and not using foreign keys is a very bad practice. It's a relational storage engine. It's meant to use foreign keys. In fact, foreign keys can help query performance[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2015 at 4:03 pm
So, what IS the PK used for?
Gerald Britton, Pluralsight courses
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply