March 15, 2023 at 6:24 pm
Is there a performance difference between a unique clustered index and a primary key clustered index?
March 15, 2023 at 7:02 pm
A clustered Primary key is a unique clustered index.
If you aren't clustering your primary key and clustering something else instead, your performance could be worse, it could be better or it could be the same. It would depend on whether the primary key is indexed at all, whether the index on the primary key includes all the columns, how the tables are being queried, etc.
I don't think I would recommend clustering something other than the primary key unless you know exactly what results you are going to get out of it. a Junction table with a primary key may be a good candidate for example.
March 15, 2023 at 7:29 pm
No. Performance-wise they are exactly the same.
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 16, 2023 at 6:11 am
This was removed by the editor as SPAM
March 16, 2023 at 2:05 pm
ok, so that's what I thought. In fact often times I'm not even creating the primary key. I simply create 1 or many unique indexes because it was my understanding that they are the same. Question: If primary key is indeed simply just a unique key then why have something called primary key?
March 16, 2023 at 2:16 pm
In certain cases or usage situations, SQL Server will require a primary key. One reason is that a primary key is guaranteed to be unique and non-NULL (that is, by definition, none of the columns involved in a PK can be NULL).
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 16, 2023 at 3:34 pm
ok, so that's what I thought. In fact often times I'm not even creating the primary key. I simply create 1 or many unique indexes because it was my understanding that they are the same. Question: If primary key is indeed simply just a unique key then why have something called primary key?
I dont think there is a justifiable argument to forgo placing a primary key on a table. If you don't need it, it doesn't harm anything but it also future proofs it against a requirement of adding a key in the future without disrupting the table. If you skip putting one on the table, it doesn't provide you with any benefit. An integer is 4 bytes, a big integer is 8 bytes. That is not a relevant amount of storage space in any database server of the last 20+ years
March 16, 2023 at 5:54 pm
A clustered Primary key is a unique clustered index.[/quote]
No, they are not the same. A primary key cannot contain a null. A unique index can contain a single null.
If you aren't clustering your primary key and clustering something else instead, your performance could be worse, it could be better or it could be the same. It would depend on whether the primary key is indexed at all, whether the index on the primary key includes all the columns, how the tables are being queried, etc.
If you are blindly recommending clustering the primary key, why? The is no correlation between a clustered index and a primary key. Far too often clustering the primary key may be the worst choice for a clustered index.
I don't think I would recommend clustering something other than the primary key unless you know exactly what results you are going to get out of it. a Junction table with a primary key may be a good candidate for example..
No, no, no. Where are you coming up with this nonsense? Let's assume you have an artificial key that is an identity or sequence. Should that be clustered when rarely is that value used in any queries.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 16, 2023 at 5:54 pm
A clustered Primary key is a unique clustered index.[/quote]
No, they are not the same. A primary key cannot contain a null. A unique index can contain a single null.
Snargables wrote:ok, so that's what I thought. In fact often times I'm not even creating the primary key. I simply create 1 or many unique indexes because it was my understanding that they are the same. Question: If primary key is indeed simply just a unique key then why have something called primary key?
If you aren't clustering your primary key and clustering something else instead, your performance could be worse, it could be better or it could be the same. It would depend on whether the primary key is indexed at all, whether the index on the primary key includes all the columns, how the tables are being queried, etc.
If you are blindly recommending clustering the primary key, why? The is no correlation between a clustered index and a primary key. Far too often clustering the primary key may be the worst choice for a clustered index.
I don't think I would recommend clustering something other than the primary key unless you know exactly what results you are going to get out of it. a Junction table with a primary key may be a good candidate for example..
No, no, no. Where are you coming up with this nonsense? Let's assume you have an artificial key that is an identity or sequence. Should that be clustered when rarely is that value used in any queries.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 16, 2023 at 5:57 pm
Did you even read any of what was written?
March 16, 2023 at 5:59 pm
I mean you literally quoted all the constraints I mentioned on it I cannot possibly imagine how you failed to understand what I wrote.
March 16, 2023 at 6:05 pm
ok, so that's what I thought. In fact often times I'm not even creating the primary key. I simply create 1 or many unique indexes because it was my understanding that they are the same. Question: If primary key is indeed simply just a unique key then why have something called primary key?
A primary key uniquely identifies a row in a table. It cannot contain any nullable columns. The need to create an artificial primary key is frequent when there are not any values in a column, or combination of columns, that are unique. A entity of "persons" is a good example. There is no single attribute or combination of attributes that uniquely identifies a person.
A unique index can contain a single null value. You can also have multiple unique indexes on a table. An example of where you may want both would be a lookup table. The PK may be defined as an identity, and a unique index may be created on the description. That would prevent the insertion of multiple rows of the same value from being inserted. The identity column is used in all of the relationships, and the description is used to display the human readable values to the users.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 16, 2023 at 6:12 pm
I mean you literally quoted all the constraints I mentioned on it I cannot possibly imagine how you failed to understand what I wrote.
I read everything you wrote. A unique index is not the same as a primary key.
You also stated 'I don't think I would recommend clustering something other than the primary key unless you know exactly what results you are going to get out of it". That reads as a blanket recommendation that the primary key should be the clustered index.
You also stated "I dont think there is a justifiable argument to forgo placing a primary key on a table.". I can argue any number of cases where a primary key should be left off. They are certainly specific examples, but again, that reads like a blanket recommendation.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 16, 2023 at 6:25 pm
Let us work on your comprehension and failures in logic and reasoning then.
I wrote "A clustered Primary key is a unique clustered index."
You suggested that meant a unique clustered index is a clustered primary key. It doesn't. That conclusion would be the same as concluding that a rectangle is a square because squares are rectangles.
I wrote that unless you know what clustering something other than the primary key will provide, you should cluster the primary key.
you suggested that meant I advised one should only cluster the primary key and that wasn't suggested anywhere. Yes, 'blindly' clustering the primary key is better than randomly putting it somewhere else for no functional reason. You also suggested I implied a correlation between the primary key and clustered index I never implied. If you have a reason to put it on something else, put it there. If you don't know why you should and can't express exactly why SQL will use it more effectively, then it probably doesn't belong there.
You wrote: "No, no, no. Where are you coming up with this nonsense? Let's assume you have an artificial key that is an identity or sequence. Should that be clustered when rarely is that value used in any queries."
in response to:
"I don't think I would recommend clustering something other than the primary key unless you know exactly what results you are going to get out of it. a Junction table with a primary key may be a good candidate for example."
Your scenario is exactly the type of scenario I alluded to. A junction table with a primary key is possibly a situation where the primary key is seldom queried but the junction columns are and would make more sense to cluster than the primary key.
March 16, 2023 at 6:42 pm
CreateIndexNonclustered wrote:I mean you literally quoted all the constraints I mentioned on it I cannot possibly imagine how you failed to understand what I wrote.
I read everything you wrote. A unique index is not the same as a primary key.
You also stated 'I don't think I would recommend clustering something other than the primary key unless you know exactly what results you are going to get out of it". That reads as a blanket recommendation that the primary key should be the clustered index.
You also stated "I dont think there is a justifiable argument to forgo placing a primary key on a table.". I can argue any number of cases where a primary key should be left off. They are certainly specific examples, but again, that reads like a blanket recommendation.
It is a blanket. Excluding a primary key provides no benefit, while excluding it can create myriad problems in the future when one is needed. a four byte integer key uses only 16 Gb of disk space when all values are exhausted. It isn't space or i/o that it is saving. If the table will truly never be related to anything else, then why is it in a relational database engine, the single most costly form of database available, and not in a cheap document database?
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply