Search

Tuesday, May 8, 2012

Move Tables Between The File Groups

1.Check  Table is exist on which filegroup

SELECT d.name AS FileGroup
 FROM sys.filegroups d
 JOIN sys.indexes i
   ON i.data_space_id = d.data_space_id
 JOIN sys.tables t
   ON t.object_id = i.object_id
WHERE i.index_id<2                     -- could be heap or a clustered table
 AND t.name= 'Employee'
 AND t.schema_id = schema_id('HumanResources')

Add another data file to the database in a separate file group.Then create the index by running the below query.

2.Create Index

CREATE UNIQUE CLUSTERED INDEX PK_Employee
    ON HumanResources.Employee(LoginID)
    WITH (DROP_EXISTING = ON) ON [Test_Data_2]

 CREATE CLUSTERED INDEX PK_Employee
    ON HumanResources.Employee(LoginID)
    WITH (DROP_EXISTING = ON) ON [Test_Data_2]

Then again execute the query-1.You will noticed the table is now pointing to the second datafile.

No comments:

Post a Comment