SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


clustered index


clustered index

Author
Message
sandhyarao49
sandhyarao49
SSC Eights!
SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)SSC Eights! (889 reputation)

Group: General Forum Members
Points: 889 Visits: 650
why can we have only one clustered index per table.
Animal Magic
Animal Magic
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7258 Visits: 13758
because a clustered index physically stores the records in that order, therefore as you can only store the data in one order you can only have one clustered index.

Non clustered indexes store pointers in a seperate file location within the data file.
Ian Yates
Ian Yates
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9518 Visits: 445
You should read up on exactly what a clustered index is rather than a regular non-clustered index and the difference will make it obvious.

Essentially a clustered index defines the physical ordering of the data in the table. Obviously the data in the table can only be ordered once - all other (non-clustered) indices store a subset of the columns in a certain order as well as storing the contents of each row as far as the clustered index is concerned. This allows the non-clustered index to do a "bookmark lookup" if need be to access the table/clustered index to retrieve other column values.



Key DBA
Key DBA
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3247 Visits: 655
sandhyarao49,


SQL Server 2005 Books Online (September 2007)
CREATE INDEX (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188783.aspx

Clustered Index
The bottom, or leaf, level of the clustered index contains the actual data rows of the table. A table or view is allowed one clustered index at a time. For more information, see Clustered Index Structures.




For further reading and/or reference ...

SQL Server 2005 Books Online (September 2007)
Clustered Index Structures
http://msdn2.microsoft.com/en-us/library/ms177443.aspx


SQL Server 2005 Books Online (September 2007)
Nonclustered Index Structures
http://msdn2.microsoft.com/en-us/library/ms177484.aspx

Happy T-SQLing,

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
I cant let you do that Dave
I cant let you do that Dave
Old Hand
Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)

Group: General Forum Members
Points: 351 Visits: 271
Why would you want to ?

The primary clustering is designed to limit the number of index pages that need to change each time a row updates onto a new data page. In this scenario only the primary clustered index would change.

Adding a second clustering index would force the system to update both of the 'clustering indexes' to point to the correct data page.

If you are not trying to limit the amount of pages touched for Update you could investigate non-clustered indexes on a table without a clustering index (or a heap). As in this case the non-clustered indexes refer to the data pages and not the primary clustering index values. (and in this scenario both would be maintained on each row update onto a new page)
EdVassie
EdVassie
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31132 Visits: 3929
A clustered index physically stores the data in the sequence given in the index definition. The way this is implemented in SQL Server means you can only have 1 clustered index per table.

This is true in most other DBMSs, but not all of them. DB2 has supported multiple clustered indexes for some years (for *nix and Windows) and now also on the mainframe. You can define a large number (256?) cluster indexes on the same table. The data is stored only once, but is physically ordered by the sequence defined for each index.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
srienstr
srienstr
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1002 Visits: 510
EdVassie (4/15/2008)
A clustered index physically stores the data in the sequence given in the index definition. The way this is implemented in SQL Server means you can only have 1 clustered index per table.

This is true in most other DBMSs, but not all of them. DB2 has supported multiple clustered indexes for some years (for *nix and Windows) and now also on the mainframe. You can define a large number (256?) cluster indexes on the same table. The data is stored only once, but is physically ordered by the sequence defined for each index.

It is possible in SQL Server, you just need to do it by way of indexed views, which results in a second copy of the table. Given the nature of clustered indices, I really don't see any way to have multiple clustered indices without storing multiple copies of the data, as it's not clustered unless it's physically stored in that order.


Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.
EdVassie
EdVassie
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31132 Visits: 3929
I really don't see any way to have multiple clustered indices without storing multiple copies of the data, as it's not clustered unless it's physically stored in that order.


In DB2, the data is stored once, and physically clustered in multiple dimensions. All it needs is a bit of lateral thinking. Consider the following...

You have a collection of objects you want to cluster by shape, by size, and by colour.

Store all the small round red things in one database extent "a".
Store all the small square blue things in extent "b".
Store all the large triangular blue things in extent "c".

Create a new type of index that only knows about extents. Call it a Multiple Dimension Clustering type of index.

Define an index for size. This has 3 entries: large, "c"; small, "a"; small, "b".
Define an index for shape. This has 3 entries: round,"a"; square,"b"; triangular,"c"
Define an index for colour. This has 3 entries: blue, "b"; blue, "c"; red,"a"

You want all the blue things, the database gets you extents "b" and "c".
You want all the small things, the database gets you extents "a" and "b"
You want the blue square things, the database gets you extent "b"
In each extent that is returned, ALL the rows match your WHERE clause.

So storing a data item once only and physically clustering it in multiple dimensions can be done. You just dedicate a whole extent to store the intersection of all your clustering indexes.

In DB2, you can set the extent size for each filegroup (called tablespace in DB2) so you can tune this value to minimise unused space in the extent. The DB2 MDC index pointers are the same size as normal RID pointers, but only the extent level information is populated, allowing normal and MDC indexes to be used in the same query with standard index AND and OR logic. This is a cool feature in DB2 but with weaknesses as well as strengths. It would be nice if SQL Server also had this technology - I am sure IBM would licence another one of its database patents to Microsoft for a suitable fee.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
srienstr
srienstr
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1002 Visits: 510
This sounds like unclustered indexes on a grouped heap. Granted, grouping the heap is an interesting idea, but I wouldn't call it multiple clustered indices.


Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.
EdVassie
EdVassie
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31132 Visits: 3929
The description I gave about MDC was very simplified. If you want more details then you have to search the DB2 documentation.

Believe me, MDC does what it says on the tin.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist. - Archbishop Hélder Câmara
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search