Possible Duplicate:
Performance difference between Primary Key and Unique Clustered Index in SQL Server
I make sure that I searched this forum but nobody asked this question before and I couldn't find any answer in anywhere too.
My question is = "What’s the difference between a primary key and a clustered index?"
Yes, you can create a clustered index on key columns that contain duplicate values.
The primary key is the default clustered index in SQL Server and MySQL. This implies a 'clustered index penalty' on all non-clustered indexes.
The primary index is created automatically when the table is created in the database. Primary key is mandatory.it avoid the duplicate of data. for ex (student rollno, material no, employee id)it should be a unique. when you create the foreign key in the particular table before it should be one primary key.
You can define keys which allow duplicate values. However, do not allow duplicates on primary keys as the value of a record's primary key must be unique.
Well, for starters, one is a key, and the other one is an index.
In most database lingo, key is something that somehow identifies the data, with no explicit relation to the storage or performance of the data. 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.
Primary key is unique identifier for record. It's responsible for unique value of this field. It's simply existing or specially created field or group of fields that uniquely identifies row.
And clustered index is data structure that improves speed of data retrieval operations through an access of ordered records. Index is copy of one part of table. It takes additional physical place on hard disk.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With