why to create only one cluster index and many noncluster index.

i am really confused on index why we should create only one cluster index with primary key table and which is not freqently accessible.and can create many non cluster index on a table which is frequently updated ,what will happen if i create many cluster index.

more ▼

asked Aug 11, 2012 at 01:30 PM in Default

avatar image

10 2 2 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

SQL Server allows you to only create 1 clustered index with 249 (999 in 2008) NCI supported.

A CI doesn't have to be the PK. If you use the default table designer within SSMS to create a PK then a CI is generated.

There is nothing stopping you dropping the CI on the PK (assuming it not on a large production env with active users would possibly see a slow down in perf) and then creating it on a more suitable column.

more ▼

answered Aug 11, 2012 at 02:37 PM

avatar image

10.9k 27 37 37

(comments are locked)
10|1200 characters needed characters left

The reason you can only create a single clustered index is that the clustered index becomes the table. When there is no clustered index present on a table, a situation referred to as a heap table, the data is stored on the disk in an unorganized fashion, using space on any pages where it can be found, with a set of lookup tables created to manage retrieving the data. When you create a clustered index, the data is stored at the leaf level of the balanced-tree of the clustered index. This means that lookups against the index also gets the data, making the cluster a much more efficient way to store and retrieve data (in most cases).

Because the cluster stores the data, you can only ever have one.

more ▼

answered Aug 13, 2012 at 06:16 PM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 11, 2012 at 01:30 PM

Seen: 994 times

Last Updated: Aug 13, 2012 at 06:16 PM

Copyright 2018 Redgate Software. Privacy Policy