How many Clustered Index can i create on a table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How many Clustered Index can i create on a table?

Author

Topic

ZarrinPour
Yak Posting Veteran

66 Posts

Posted - 2007-11-14 : 13:18:05

Hi all
as i remember i had read in Books Online that on each Table in Sql Server we can create only one Clustered index
but today suddenly i create another clustered index on a table without any Error from SQl server !!!
BUT my query Order changed to the order of this newly created index.
could anyone elaborate on this issue?

Thanks in advance.
Regards.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 13:22:31

Please post your table data, your table layout and your INDEX creation statement.

E 12°55'05.25"
N 56°04'39.16"

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-14 : 22:25:02

Each table can have only one clustered index. Your table may have non-clustered pkey.

ZarrinPour
Yak Posting Veteran

66 Posts

Posted - 2007-11-15 : 02:06:21

quote:
Originally posted by rmiao

Each table can have only one clustered index. Your table may have non-clustered pkey.

Hi rmiao
Thanks you for ur reply
yes,You are right , i was wrong and i didn't noticed that ihad a non-clustered Primary Key.

Thank you GUYS
Regards.

kishore chittam
Starting Member

10 Posts

Posted - 2007-11-18 : 20:37:27

Clustered index
Reorders the way records in the table are physically stored. Therefore table can have only one clustered index.
The leafnodes of clustered index contain the data pages.
Table and index are same
Non- clustered index

In which the logical order of the index does not match the physical stored order of the rows on disk

The leafnode of non clustered index does not consist of the data pages. Instead , the leaf nodes contain index rows.
Table and index are different from each other

quote:
Originally posted by ZarrinPour

Hi all
as i remember i had read in Books Online that on each Table in Sql Server we can create only one Clustered index
but today suddenly i create another clustered index on a table without any Error from SQl server !!!
BUT my query Order changed to the order of this newly created index.
could anyone elaborate on this issue?

Thanks in advance.
Regards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources