Great post! What does matter is that a table may only have one clustered index as this defines the physical order of the table. I wonder if it is worth more explicit treatment that the primary key will only default to a clustered index if another clustered index does not already exist? Ultimately I think the SQL Server terminology was a bad choice since that is where the confusion starts, plus enforcing a default behaviour of clustered index.
Thanks for your comment. You still want to enforce the primary key, but this time with a Non-Clustered Index. Why would you ever want to cluster on a random value like a unique identifier? What makes more sense is to have the primary key a unique identifier and cluster on an incrementing value. Like you said, the clustered index determines the physical structure. Why not make the unique identifier the primary key? Now every time you try to get rows by the primary key, it needs to do another index lookup to get the clustered index value first.
And a primary key is a piece of data that uniquely identifies that data. An index on the other hand is something that describes a faster way to access data. It does not generally concern itself with the integrity and meaning of the data, it's just concerned with performance and storage.
In SQL Server specifically, a clustered index is an index that dictates the physical order of storage of the rows. The things that it does are quite complex, but a useful approximation is that the rows are ordered by the value of the clustered index. This means that when you do not specify a order clause, the data is likely to be sorted by the value of the clustered index. So, they are completely different things, that kinda-sorta compliment each other.
That is why SQL Server, when you create a primary key via the designer, throws in a free clustered index along with it. Before you can ask the difference between primary key and clustered index, you have to know that a key and an index are not the same thing.
A key can be a primary key or a foreign key. There can be only one primary key per table but it might be more than one column. A key is a logical thing, it serves the business logic and defines the integrity of data. A foreign key is a reference to a primary key of another table. Indexes helps to speed up your queries, because it builds references to columns of your choice. So it creates separate files that helps your queries that use indexed columns. The Index itself has what are called "key values.
For example, some applications create their own keys or GUIDs in order to relate different tables through foreign keys.
Your question has many more questions. I think we need to break this down into many questions, since you're asking about a lot of index concepts. There's little or no 'real' tangible difference between the two. The ability to define them separately i. Default behaviour if not otherwise specified is to cluster on the primary key; but there's no need to let that physical design choice define the choice of primary key because you can define a clustered index that's not the default key.
As suggested in ArthurD's comment there may be two ways where either way might arguably be used, with no clear-cut preference for one or the other.
There's a specific, theoretical definition of what a 'primary key' is: see What does this definition of a Primary key mean? So for example if itemId were unique then userId,itemId would not should not or could not be a primary key: so itemId would be the primary key, and the clustering that you want on userId,itemId would be on a non-primary-key index. Unique clustered index : when created, it creates an index not any other key and also enforce the uniqueness for the key columns used.
Because by definition primary key is unique, it can be clustered or non-clustered without loosing its uniqueness. Sign up to join this community. The best answers are voted up and rise to the top. Stack Overflow for Teams — Collaborate and share knowledge with a private group.
Create a free Team What is Teams? Learn more. Difference between a unique clustered index and a clustered primary key? Ask Question. Asked 5 years, 2 months ago. Active 4 years, 11 months ago. Viewed 4k times. Old data is then at one end of the table and new data at the other end of the table. Another plus for using the identity property is that it is an integer and keeps foreign key relationships and indexes to a relatively small size.
However, while researching how best to define a partition key for large tables — further discussed in Part 3 of this article , it finally dawned on me that perhaps it might be better to break apart the Primary Key i. In the instance the CreateDateKey is an integer of the form yyyymmdd. Consider using the CreateDateKey as defined above and the Primary Key for the clustered index for the table instead of the original definition:. Need to persist CreateDateKey to use as Clustered index—this causes a schema lock.
This is necessary for the definition of the SQL Server clustered index. It will fail if there are any other tables with foreign key relationships to dbo. Obviously this is unique since the original Primary Key is unique.
0コメント