x

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

abcd123 gravatar image

abcd123
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

sp_lock gravatar image

sp_lock
9.2k 25 28 31

(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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1948
x130

asked: Aug 11, 2012 at 01:30 PM

Seen: 767 times

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