Post reply

Clustered Index Space

  • February 13, 2013 at 2:25 am

    #274058

    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

  • February 13, 2013 at 2:29 am

    #1587329

    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.

  • February 13, 2013 at 2:37 am

    #1587330

    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

  • February 13, 2013 at 2:39 am

    #1587331

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

  • February 13, 2013 at 2:42 am

    #1587333

    anthony.green (2/13/2013)

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

    Great Stuff - Thanks for your input

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply