Primary key Index,Cluster Index & Non-Cluster Index in AX 2012

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.

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.

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.

Meet you in next post…

Till then, Happy Daxing………………….

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: