May 2, 2011 at 2:53 am
Hi All,
I using following script to find out the object on the filegroup.
--*****
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = f.data_space_id
AND o.type = 'U
--*****
Its output as following
aaaU NULL 0FG
aaaU IX_aaa_a12FG
aaaU PK_aaa.a23FG
Now i want to move object aaa to new filegroup FG1 i have move the indexes which exist on aaa with index_id=2 and 3
If now again i run the above script the output is as below
aaaU NULL 0FG
aaaU IX_aaa_a12FG1
aaaU PK_aaa.a23FG1
What @ index_id=0 as it resides on the same filegroup FG. Does the table moved completely or not.
Please guid
May 2, 2011 at 5:01 am
Indexes and tables are different objects and can reside on different file groups. In your case this is exactly what happened. You moved the noneclustered indexes to a different file group, but you didn’t move the table. If you want to move the table, you can create a clustered index and specify that you want the clustered index to be created on the new file group. If you don’t want the clustered index you can later drop it, and the table will stay on the new file group. Notice that creating and dropping clustered index has its own price in terms of resources, locking, etc’ and you should take it into consideration.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 2, 2011 at 5:20 am
Thanks Adi,
I did the same thing:-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply