Clustered Index Space

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Clustered Index Space

Clustered Index Space

Rate Topic

Display Mode

Topic Options

Author

Message

SQLSACT

Posted Wednesday, February 13, 2013 2:25 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:44 AM
Points: 1,185, Visits: 2,099

Hi All

I understand that when you create a clustered index, SQL Server doesn't create a physical structure seperate from the table.

Initially I though that this can't be right because SQL allows you to create a table in one filegroup and then create the Clustered index in a different filegroup. I tested this and saw that when you create a Clustered index on a table in a different filegroup, SQL moves the table to the filegroup that contains the clustered index.

My question> Why is it that after creating a clustered index, the size of the table increases slightly? Is this perhaps the pages in the non-leaf levels of the B-Tree?

Thanks

Post #1419353

anthony.green

Posted Wednesday, February 13, 2013 2:29 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

It needs somewhere to store the B-Tree so you would expect it to be slighltly larger after changing from a heap to a clustered index.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1419354

SQLSACT

Posted Wednesday, February 13, 2013 2:37 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:44 AM
Points: 1,185, Visits: 2,099

anthony.green (2/13/2013)

It needs somewhere to store the B-Tree so you would expect it to be slighltly larger after changing from a heap to a clustered index.

Thanks Anthony

Would you agree that a Clustered Index is absolutely NOT a seperate structure from your table?

Thanks

Post #1419355

anthony.green

Posted Wednesday, February 13, 2013 2:39 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Yes I would aggree as the clustered index IS your table.

Post #1419356

SQLSACT

Posted Wednesday, February 13, 2013 2:42 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:44 AM
Points: 1,185, Visits: 2,099

anthony.green (2/13/2013)

Yes I would aggree as the clustered index IS your table.

Great Stuff - Thanks for your input

Post #1419358

« Prev Topic | Next Topic »

Permissions