Efficient way of doing ??

Question

Hi 

I have to move  all the tables in a database from one file group to another file  group.

All my tables have millions of records and the indexes are in correct file group but not the tables. 

What is the efficient way of doing this?

Please also tell me How much time will it take to complete the whole process ?

Answer 1

Three times as long as two bananas worth of shellfish.

Since we've no way to tell how large the amount of data you're referencing is (never mind what hardware is at play), we can't even begin to guess.

---

Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
Really enjoyed it? See my profile!
My Tech Net Articles.

Answer 2

Hi,

1. If table has Clustered index 

drop clustered index and  recreate clustered index in new file group

note: if primary key is clustered then you need to drop and recreate references first.

2. If table is heap.

   create clustered index on the new filegroup and drop it 

It can also be done in online mode subject to certain conditions. See BOL  for details.

The time taken can only be determined based on the size of your table.

Please go through the below link for further information on this topic.

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/sql-server-filegroups-the-what/

---

Aparna

Answer 3

Which Indexes are "in the correct Filegroup"?   If your CLUSTERED INDEX is in the correct Filegroup then so is your table (the clustered index IS the table) so you are fine and have nothing more to do.

If your Non-Clustered indexes are in the correct Filegroups, but your Clustered index or heap is not, then you either:

1) if a clustered index exists, then simply rebuild it and specify the new filegrouop in the "ON" clause of the statement.

2) if you have a heap then simply build a clustered index on the correct filegroup and then subsequently drop the clustered index

If you need to keep the original tables online during the operation, you could do a SELECT INTO and make a full copy and then rename the two tables and recreate any additional indexes.

---

Martin Cairney SQL Server MVP

Answer 4

What is the efficient way of doing this?

You can move indexes with CREATE INDEX...WITH(DROP_EXISTING=ON).  This will avoid sorting and, in the case of the clustered index, avoid rebuilding the non-clustered indexes too.

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Answer 5

Hi,

I have two file groups called SRI_CORE and SRI_INDEX.

Whenever I go the table properties and see the storage option  the file group that is mentioned is SRI_INDEX and when I check the Index properties and check the storage the file group it is also SRI_INDEX  which is correct.

So The thing I wanted to do is to point the Table storage to SRI_CORE and the index storage should remain as it is.

Note. The table have only one index that is clustered Index based on the Primary key.

Please suggest me how can I move the table storage to SRI_CORE and the index storage should remain as SRI_INDEX

Answer 6

If you ONLY have a clustered index then the Table and the Clustered Index are one and the same thing - they cannot be separated as they are NOT two distinct objects.

Read this to see if it clarifies things :

http://www.practicalsqldba.com/2013/03/sql-server-explaining-clustered-table.html

---

Martin Cairney SQL Server MVP

Answer 7

Hi Martin, 

If both are same  . Then what could be the reason for separate file groups.

There are some other tables  related to other modules of the application where the table storage is stored in SRI_CORE  and the index  is stored in SRI_INDEX.

Remember all our Indexes (i.e both clustered and Non clustered is stored in the right file group)

Our DBA have raised a issue with us that we need to change the table storage from SRI_INDEX to SRI_CORE.

What should be my  reply ?

Answer 8

Hi Rehan,

For a table which has a clustered index, the data pages are part(actually the left nodes) of the index structure. You can leave the clustered index in SRI_CORE, for any nonclustered indexes on the same table, you can create them in SRI_INDEX.

See the difference between clustered and nonclustered index. In a nonclustered index, the index leaf nodes point to data pages while in a clustered index, the left nodes are data pages.
Clustered Index Structures
Nonclustered Index Structures

---

Eric Zhang
TechNet Community Support

Answer 9

"If both are same  . Then what could be the reason for separate file groups."

The clustered index *is* the table. If somebody tries to convince you differently, then point that person to this thread and kindly tell that person that he/she is incorrect.

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 10

Hi Eric,

All the  clustered index and non clustered  index are on the same file group (i.e SRI_INDEX). Only thing I wanted to migrate is the table storage to SRI_CORE

Image 1 which is for index ,I checked this bu right clicking the index properties and then clicking the storage property

This is referring to SRI_INDEX which is correct and there is no need of any change

Image two I checked with and saw the table properties and then checked the storage property which is also pointing to the same file group SRI_INDEX .This is where I need to change from SRI_INDEX to SRI_CORE

As I said earlier our DBA wants this table storage  property to change from SRI_INDEX to SRI_CORE .So Should I change this to sync with the tables of other modules of the application or leave it as it is.

What shall i reply to the DBA or if I have to change it then what are the steps to follow.

Please let me know if you need any other information

Answer 11

"All the  clustered index and non clustered  index are on the same file group (i.e SRI_INDEX). Only thing I wanted to migrate is the table storage to SRI_CORE"

And we have repeatedly told you that the "table storage" and the clustered index storage is the same thing and cannot be separated. The "table storage" is where the "clustered index storage" is, they cannot be separated (and vice versa, of course).

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 12

Hi Tibor,

I completely agree with your answer . I am not a expert in all these stuff.

The only thing that worries me when I see the other table's properties from another module of the application.There the table storage point to SRI_CORE and the index(i.e Both Clustered and non clustered) storage points to SRI_INDEX

So Please tell me if they are wrong or there is some other reason for that ?

Answer 13

So you are telling me that SQL Server management studio will show one filegroup for the "table storage" and a different file group for that table's clustered index filegroup?

If that is the case, then you have a bug in SQL Server Management Studio. I tried to reproduce it, but couldn't:

CREATE DATABASE [x]
 ON  PRIMARY 
( NAME = N'x', FILENAME = N'C:\DemoDatabases\DbFiles\p\x.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB ), 
 FILEGROUP [FG2] 
( NAME = N'x_2', FILENAME = N'C:\DemoDatabases\DbFiles\p\x_2.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'x_log', FILENAME = N'C:\DemoDatabases\DbFiles\p\x_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
USE x

--Table where both cl and nc index are on PRIMARY
CREATE TABLE t(c1 int identity, c2 char(10))
CREATE CLUSTERED INDEX t_cl ON t(c1) ON [PRIMARY]
CREATE NONCLUSTERED INDEX t_nc ON t(c2) ON [PRIMARY]

--Table where both cl and nc index are on FG2
CREATE TABLE t2(c1 int identity, c2 char(10))
CREATE CLUSTERED INDEX t_cl ON t2(c1) ON FG2
CREATE NONCLUSTERED INDEX t_nc ON t2(c2) ON FG2

--Table without CL is which is on PRIMARY and NC index is on FG2
CREATE TABLE t3(c1 int identity, c2 char(10))
CREATE NONCLUSTERED INDEX t_nc ON t3(c2) ON FG2

--Table without CL is which is on FG2 and NC index is on FG2
CREATE TABLE t4(c1 int identity, c2 char(10)) ON FG2
CREATE NONCLUSTERED INDEX t_nc ON t4(c2) ON FG2

---

Tibor Karaszi, SQL Server MVP | web | blog