why only one clustered index per table

Question

1.00/5 (1 vote)

See more:

SQL-Server-2005

SQL-Server

why only one clustered index per table plz explain with reason

Posted 18-Oct-13 8:59am

Member 8031915

Updated 11-Jan-17 21:33pm

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

André Kraak · Answer 1 · 2013-10-18T09:05:00

Quote Microsoft[^]:
A clustered index sorts and stores the data rows in the table based on the index key values. Therefore only one clustered index can be created on each table because the data rows themselves can only be sorted in one order.

To create a different clustered index on the same table, change the Create as Clustered property setting on the existing clustered index before creating the second index.

For more information, see How to: Change Index Properties (Visual Database Tools)[^] and How to: Create Clustered Indexes (Visual Database Tools)[^].

Member 12946447 · Answer 2 · 2017-01-11T21:33:00

Hi All,

Cluster index are sorted physically ,the data at the leaf node are sorted on only one particular order since there will be only one cluster index can be created and having its index table associate with leaf node(data page).
Cluster index can also be created for two columns(composite cluster indexing) also,but only one clustered index per table.

Example for cluster index :Telephone book is ordered in the alphabetic way, for example there are two names called raju then it will sort first with first name and then with last name

Non-Clustered index:The data is in leaf node(data page),and are not in order where the row pointer are pointing to the leaf node where the data is located.
the table can have more then one non cluster indexing.

Example for non cluster indexing :Index on books in front page,where the page number points to that particular topic in the book,But it is not ordered in the one particular way.A table can have more then one non cluster index.

Thanks..

Sachin Jaat · Answer 3 · 2015-08-27T04:42:00

Clustered Index: Clustered index defines the way in which data is ordered physically on the disk. And there can only be one way in which you can order the data physically. Hence there can only be one clustered index per table.

Why care about clustered index? If we put clustered index on the table then the data is retrieved much more faster because sql server doesnot have to read the whole data -- Depends on query. But data retrieval is much more faster.

NOTE: Although you can create more than one Non-Clustered index on a single table.

why only one clustered index per table

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0