Primary Key:
The fields in the primary key uniquely identify records in any given table.
Each unique combination of primary key fields uniquely identifies one (and only
one) record in the table. Primary keys MUST be unique. There can only be ONE
primary key per table. Not all unique indexes are primary keys.
Cluster Index:
The Clustered index is the physical order in which records are stored in the table. Say you have a clustered index on a text field called “Name”, and you insert a record with a name that starts with a letter D, the record will physically be inserted between the C and the E record. Clustered indexes do not have to be unique.
The Clustered index is the physical order in which records are stored in the table. Say you have a clustered index on a text field called “Name”, and you insert a record with a name that starts with a letter D, the record will physically be inserted between the C and the E record. Clustered indexes do not have to be unique.
Suppose we have a table
named Employee which has a column named EmployeeID. Let’s say we create a
clustered index on the EmployeeID column. What happens when we create this
clustered index? Well, all of the rows inside the Employee table will be physically –
sorted (on the actual disk) – by the values inside the EmployeeID column. What
does this accomplish? Well, it means that whenever a search for a sequence of
EmployeeID’s is done using that clustered index, then the search will be
much faster because of the fact that the sequence of employee ID’s are
physically stored right next to each other on disk – that is the advantage with
the clustered index. This is because the rows in the table are sorted
in the exact same order as the clustered index, and the actual table data
is stored in the leaf nodes of the clustered index.
Primary Index
The Primary Index of a table is the main index that is used to uniquely identify records in it. No duplicated values are allowed in this index and all fields in the index are required. You can designate a clustered or normal index as the primary index. Since the primary index is the main index you should gain optimum performance by designating a clustered index for this purpose.
The Primary Index of a table is the main index that is used to uniquely identify records in it. No duplicated values are allowed in this index and all fields in the index are required. You can designate a clustered or normal index as the primary index. Since the primary index is the main index you should gain optimum performance by designating a clustered index for this purpose.
Clustered index dictates
the physical storage order of the data in the table.
A table can have multiple
non-clustered indexes because
they don’t affect the order in which the rows are stored on disk like clustered
indexes.
Why can a
table have only one clustered index?
Because a clustered index determines the order in which the rows will be stored on disk, having more than one clustered index on one table is impossible. Imagine if we have two clustered indexes on a single table – which index would determine the order in which the rows will be stored? Since the rows of a table can only be sorted to follow just one index, having more than one clustered index is not allowed.
Thanks for comments.....