Moving the table to new file group in SQL Server

The below link will explain how to move table from one file group to another file group.

How to move table from one filegroup to another

After the link opens search with the below text to go to the respective topic.

D. Dropping a clustered index online and moving the table to a new filegroup. In the below example I am moving Person.Person table to new file group and new data file and new drive.


USE master
go
ALTER DATABASE [AdventureWorks2014]
ADD FILEGROUP MyGroup
go
ALTER DATABASE [AdventureWorks2014]
ADD FILE (NAME='MyDataFile_Data',
FILENAME='G:\AdventureWorks\NEWFILEGROUP\MyDataFile_data.ndf')
TO FILEGROUP MyGroup
GO
/*
The below piece of code will move the table from pirmary to newly created
file group called "MyGroup". And in this piece of code "UNIQUE" is compusory
However this is not referring to UNIQUE index. This command may throw an error if the table has
"XML" indexes.Scriptout those tables drop it and recreated them after you moved the table to
new file group
*/
CREATE UNIQUE CLUSTERED INDEX [PK_Person_BusinessEntityID]
ON [Person].[Person](BusinessEntityID)WITH(DROP_EXISTING=ON)
ON MyGroup
GO
SELECT * from [Person].[Person]





Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server