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

Last Post 24 Aug 2012 04:11 PM by gunneyk. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
rasgueado
New Member
New Member

--
24 Aug 2012 06:48 AM
Hi

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 index statement will say that it completed but it happens very quickly (even for tables 1 GB and greater) and if I look at the datafiles that are used by the filegroup it does not look like the index was infact moved. If I look at the storage for the 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!
gunneyk
New Member
New Member

--
24 Aug 2012 04:11 PM
Can you post the actual script you used to do the move? Basically the way to move it is to simpy specify a new filegroup in the Create Index and use hte DROP EXISITING clause. The datatype of the columns (unless they are offrow lobs) should not be a factor. However any nonclustered indexes on the tables will continue to stay in the filegroup they are in and will not be afected by the move of the clustered index.


Acceptable Use Policy
---