DesmoShane (11/6/2012)

---

I would like to move a clustered index from one filegroup to another on a production database without causing any disruption for users and was wondering if this is the recommended process for doing this

sql version is 2008 R2 Enterprise

The table has non clustered indexes in a different filegroup so the clustered index is the only data in this filegroup. The options i think should work are are in the attached script

USE [Database_Name]
GO
CREATE CLUSTERED INDEX [Clustered_Index_Name] ON [dbo].[Table_Name]
(
[Column_1_Name] ASC,
[Column_2_Name] ASC,
[Column_3_Name] ASC,
[Column_4_Name] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = ON, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
GO


Can someone advise if doing this (apart from cpu\memory usage) will cause any unexpected issues with the user experience on the database that this is being run on and if i am aproaching this in a recommended or best practice fashion

will the resulting non-clustered indexes will they be rebuilt automaticaly and be an online operation

DesmoShane (11/6/2012)

---

will the resulting non-clustered indexes will they be rebuilt automaticaly and be an online operation

Bhuvnesh (11/7/2012)

---

YEs. any change to the cluster keys forces all the non-clustered indexes to be rebuilt after the new clustered index has been created. but try to perform this on off-peak hours

sanket kokane (11/7/2012)

---

I don't thinks so.

look at the Paul's blog ... Myth No.3

http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-19-Misconceptions-around-index-rebuilds-%28allocation-BULK_LOGGED-mode-locking%29.aspx