April 14, 2020 at 2:26 pm
All,
I would appreciate views on the following:
When the unique combination for table involves several columns is it best to:
a) Make the primary key include these three columns
b) Make a unique reference (such as a guid) as the primary key and use a constraint, on the original columns, to enforce the uniqueness?
If I use option a) then if another table references this one then it has to include all the extra columns which uses extra space and someone could miss a column from the join. However if I use option b) then I'm adding an extra column and kind of moving away from pure normalisation?
I've always used option b) and it's never caused me any issues but I would interested in other opinions. From memory I used b) as each table required a guid for replication but that could be incorrect? I tend to use availability groups now and, as far as I know, that restriction doesn't apply.
I'm not sure if there is a clear answer, which I've forgotten, or whether it is a case of "it depends"?
Thanks
April 14, 2020 at 3:20 pm
It's fine to create a primary key constraint on multiple columns. You'll want to choose your clustered index key more carefully, though. The clustered index is by default (in most circumstances) created on the primary key, but you don't have to do it like that, and indeed in many cases it's better to have your primary key non-clustered. Be careful with GUIDs, especially if you're thinking of using one as your clustering key - you may find yourself with a lot of expensive page splits.
There's a lot more to it than what I've said above - far too much to put in a forum post. And yes, it does depend, not least on personal preferences. If you're (un)lucky, you'll spark a debate on natural versus surrogate keys, GUIDs versus integers and all sorts of other things that people hold quasi-religious views on. The best thing to do is read about it and make up your own mind.
John
April 16, 2020 at 12:52 pm
Thank you for your help.
October 24, 2021 at 9:09 pm
I would not advise a GUID as a primary key. They are 16bytes in size last time I checked, have no fixed order, and a int or big int could do the same work with a smaller size and while being self incrementing. You can fragment that GUID index not long after a rebuild (depending on your writes) to where the gains from from the rebuild would not last long.
----------------------------------------------------
October 25, 2021 at 8:31 am
As far as Primary Key (without Clustered Index) is concerned you MAY have it on a GUID or an Identity column (mostly preferred). But if you ask Primary Key with Clustered Index then it won't make sense to have it on GUID, unless you have a solid reason to do so.
You may have a look at one of my articles as follows. It is dedicated to Clustered Index with some experiments and somehow covers your question to some extent.
https://sqlservercarpenter.com/2020/04/28/a-deep-dive-on-clustered-index/
October 25, 2021 at 11:58 am
"Black Arts" Index Maintenance - GUIDs v.s. Fragmentation - Jeff Moden - YouTube
October 27, 2021 at 2:24 am
As far as Primary Key (without Clustered Index) is concerned you MAY have it on a GUID or an Identity column (mostly preferred). But if you ask Primary Key with Clustered Index then it won't make sense to have it on GUID, unless you have a solid reason to do so.
You may have a look at one of my articles as follows. It is dedicated to Clustered Index with some experiments and somehow covers your question to some extent.
https://sqlservercarpenter.com/2020/04/28/a-deep-dive-on-clustered-index/%5B/quote%5D
I am just not sure I would prefer to do joins across tables with a guid as my join column, even if it is not the clustered index. Guids represent an attempt to almost guarantee no collision when unionizing two tables across different systems into a new datawareshouse, for instance. If it can be helped, it I would prefer to not use them as key columns.
----------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply