February 19, 2014 at 3:10 pm
I have a table where a combination of multiple columns define the business uniqueness. The table has an identity column. A sample is like this:
create table Tbl
(
IndexID int identity(1,1),
ID1 int,
ID2 int,
ID3 int,
-- Many other columns
)
Apart from identity column, 99% of the time uniqueness is the combination of ID1, ID2 and ID3. I can have millions of distinct ID1's. For each ID1 there are 10 or so ID2's, and for each ID2 there are 10 or so ID3's.
I can create a clustered primary key on the table with ID1, ID2, ID2 and IndexID. Instead, I'm thinking of creating a PK that is simply ID1 and IndexID. This makes my clustered index narrower, faster and taking less space. But that also means I need to scan over 100 (10x10) or so records every time I need to retrieve data where ID1, ID2 and ID3 are known.
Does anyone know the real-world trade-off between narrower PK (ID1 and IndexID) and more granular PK (ID1, ID2, ID3 and IndexID)?
Thanks
February 19, 2014 at 3:27 pm
What about making your identity column the primary key and creating a clustered index for ID1, ID2, ID3?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 19, 2014 at 3:38 pm
Thanks for the answer.
What would be the benefit of creating a non-clustered PK on IndexID and a clustered index on ID1, ID2 and ID3 over the other two options? In particular, is there any benefit to that over creating a clustered PK on ID1, ID2, ID3 and IndexID, with IndexID being the last key of the index just to guarantee uniqueness?
February 19, 2014 at 3:42 pm
N_Muller (2/19/2014)
Thanks for the answer.What would be the benefit of creating a non-clustered PK on IndexID and a clustered index on ID1, ID2 and ID3 over the other two options? In particular, is there any benefit to that over creating a clustered PK on ID1, ID2, ID3 and IndexID, with IndexID being the last key of the index just to guarantee uniqueness?
Not sure there would be any benefit per se. It seems a little weird that you have a composite key but you want to include an identity column in addition. Most likely the best choice would be to just drop the identity column entirely as it is just clutter since you already have your key.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 19, 2014 at 3:42 pm
N_Muller (2/19/2014)
Does anyone know the real-world trade-off between narrower PK (ID1 and IndexID) and more granular PK (ID1, ID2, ID3 and IndexID)?
Yeah, a smaller index. Also, you can't get more granular than unique per row... which is IndexID. None of the other fields are necessary for the key.
The key, however, is just how you find a unique row in a table. Thus Sean's suggestion. Since IndexID alone will do that, that's all you need for your key.
The Clustered Index, however, is what you hang the sorting of the table off of, all the other index's references to the table, and the primary search mechanism for the leaf layer of the table. It should usually support your most common joins for best optimization. Exceptions, as always, can apply.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 19, 2014 at 4:00 pm
I also want to mention that this was just an example and the business uniqueness requires combining 6 columns.
The identity column is needed because I may get an odd case here and there where the combination of ID1, ID2 and ID3 won't be unique. Narrow keys is what I thought would be best as well.
If I understand correctly, I want to keep narrow indexes, just as I thought. So I have basically two options: 1) Create a non-clustered PK on IndexID and non-unique clustered index on ID1 only, or 2) create a clustered PK on ID1 and IndexID. Either case won't provide me with row-level granularity according to the business process requirement (for that it needs ID2 and ID3), but the performance benefit of the narrower key may outweigh the wider PK or index.
Thanks again
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply