Clustered vs Non clustered index

Clustered Index

1. A table can contain only one clustered index

2. On creation of primary key clustered index will be created automatically

3. Clustered index store the data in sequential order Dec /Asc

4. Clustered index can be applied to more than one columns that are called composite clustered index

Example Composite clustered index:

Create clustered index  emp_name_email on Employee(Name desc,Email asc)

Non clustered index

Non clustered index store in the different table with index column and row address
———————

ID Name      Email

———————————

1  A      g@gmail.com

2  B      b@gmail.com

3  C      c@gmail.com

———————————
Non clustered index table

————————

Name RowAddressed

————————

B             Row Address

————————
A clustered index is faster then nonclustered index why?

because of nonclustered index store data in the seprate table so it has to look to the main table for looking for other columns details so it will require on more lookup to the master table.

 

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