This
is one of the questions haunting me from long where-in I was not aware of how
to do that and I haven’t spent time as well to find the solution, luckily i tried this time. First thing is(as far as I tried) we cannot move a table from one file to another file in
a database but what we can achieve is we can move a table between file groups. Something
like from PRIMARY file group to SECONDARY and so on.
How?
To
move a table from Filegroup1 to Filegroup2 we have to move the CLUSTERED
INDEX of the table. After that we need to move the other NON-CLUSTERED indexes
as well to the new file group. This will move the entire table from one FG to another
FG.
Here
I will move a table named ‘SalesOrderDetail’ of ‘AdventureWorks2012’ sample database
from PRIMARY file group to SECONDARY file group. The below commands I have
tried on both 2008 and 2012, they are valid for both versions.
Step: 1
To get Index Details:
To
get the details of indexes along with file group information I am using below
query. It will give table name, type of index, index name and in which file
group it is currently in.
SELECT 'Table Name'
= OBJECT_NAME(INX.object_id),
'Index
Name' = INX.name,
'Type Of
Index' = INX.type_desc,
'FileGroup
Name' = FGS.name
FROM sys.indexes INX
INNER JOIN sys.filegroups FGS ON
INX.data_space_id =
FGS.data_space_id
WHERE INX.object_id = OBJECT_ID('[Sales].[SalesOrderDetail]')
As
we can see in above capture the table has 3 indexes and all are in PRIMARY FG.
Creation of
secondary file group and secondary file:
To
create SECONDARY file group we can right click on the database and select ‘Properties’,
then click on ‘Filegroups’, then click on ‘Add’ button and under the ‘Rows’
enter the name of the new file group, here I am giving the name as ‘SECONDARY’
itself and click ‘OK’. This will create new file group.
Adding file to new
FG:
To
create secondary data file and add it to new FG we can use below command:
Use
AdventureWorks2012
ALTER DATABASE AdventureWorks2012
ADD FILE
( NAME = [AdventureWorksDW2008R2_Data2],
FILENAME = 'C:\SQLServer2012\AdventureWorks2012_Data2.mdf')
TO FILEGROUP
[SECONDARY]
While
creation itself we can specify the settings of the new data file like how it
should grow, what should be its initial size and all. If we don’t specify
those values it will take all default values. Through the above command a new file
will be created with the name ‘AdventureWorks2012_Data2’ and it will reside
in the location specified.
Moving of CLUSTERED
INDEX:
After
creating secondary FG and adding file to it , to move table ‘SalesOrderDetail’ to new
FG we first need to move CLUSTERED INDEX of that table with below command:
CREATE UNIQUE CLUSTERED INDEX
[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]
ON [Sales].[SalesOrderDetail]
(
[SalesOrderID] ASC,
[SalesOrderDetailID] ASC
) WITH (DROP_EXISTING = ON,PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY
= OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS
= ON)
ON [SECONDARY]
In
the above command the 2 things important are DROP_EXISTING = ON this will
create new one by dropping the existing one and ON [SECONDARY] this will create
it in SECONDARY file group. With above command we are trying to move the
‘[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]’
to new FG. The index names
can be obtained from same query of Step:1.
Note: If the table is very huge
it will be faster if we truncate (Depends on wether you are allowed or not to TRUNCATE) the table before moving.
Once the command completes
successfully if we run the query of step 1 it will give below results:
As
we can see the CLUSTERED INDEX has been
moved to new FG. Now in the same way we need to move the other NON-CLUSTERED indexes
as well to new FG.
CREATE UNIQUE NONCLUSTERED INDEX [AK_SalesOrderDetail_rowguid] ON [Sales].[SalesOrderDetail]
(
[rowguid] ASC
)WITH
(PAD_INDEX
= OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY
= OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [SECONDARY]
GO
CREATE NONCLUSTERED INDEX
[IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail]
(
[ProductID] ASC
)WITH
(PAD_INDEX
= OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [SECONDARY]
GO
After
moving NON-CLUSTERED indexes as well we can verify with same query:
In
capture we can see now all the indexes have been moved to new FG.
The
same has to be done for all the indexes of the table. For moving of
NON-CLUSTERED indexes just script out the existing indexes and set the 2
options as DROP_EXISTING = ON and ‘ON [SECONDARY]’.
Thanks!!
