Clustered index that contains Unique identifier and moving that Index to a new Filegroup

Last Post 28 Aug 2012 07:32 AM by rm. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Not Resolved
rasgueado
New Member
New Member

--
27 Aug 2012 10:10 AM
I'm looking to see if anyone has had any experience in moving clustered indexes to new filegroups where that index contained a uniqueindentifier column. In my testing so far it seem to me that after dropping and recreating the clustered index to a new filegroup that create index statement will say that it completed but it happens very quickly. Much more quick than I would expect the rebuilding of a 1 GB table to take. If I look at the datafiles that are used by the new filegroup by intially leaving them very small in size and then look at them again after the index rebuild their size does not grow in proportion to the size of the table. If I look at the storage for the clustered index it will say it is on the new filegroup but that does not appear to be the case. I have tried this both on a SQL Server 2008 standard edition installation and on a SQL Server 2008 R2 standard edition installation. Any insight from anyone would be greatly appreciated.

Thanks
rm
New Member
New Member

--
28 Aug 2012 07:32 AM
When you create clustered index on specific file group, sql moves data of the table to that file group. You can double check with 'sp_spaceuser table_name'.


Acceptable Use Policy
---