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.
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