Moving Table to different Filegroups

Question

Moving Table to different Filegroups

anil702

Say Hey Kid

Points: 670
More actions
September 3, 2010 at 4:28 am

#146827

How to move tables from primary file group to secondary file group
Tables with (NO clustered/non clustered index) ----sample code
Table with clustered index and non clustered index -------sample code
need sample T-Sql script
and how can we check tables are in secondary file group
-----i have checked in many sites---------
http://www.mssqltips.com/tip.asp?tip=1112
Table is not mentioned
-------
i want step by step to move tables from primary file group to secondary file group
Please revert back ASAP for this topic

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

NJ-DBA SSChampion Points: 13834 More actions · Answer 1

You can not move a file to a different file group. You can move a table to a different file group. To do so you need to add a clustered index (you can drop it again if you absolutely must not have one, but in general its safe to say that every table should really have a clustered index.... 99% of the time anyways).

example:

ALTER DATABASE yourdb

ADD FILEGROUP newfg

ALTER DATABASE yourdb

ADD FILE

(

NAME = Dtreedatafile1,

FILENAME = 'D:\Dataewfile.ndf',

SIZE = 1024MB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 500MB

) TO FILEGROUP newfg;

GO

CREATE CLUSTERED INDEX indextablecol ON table(idcolumn) ON newfg

edit: just to add clarity- by creating the clustered index, you are actually reordering the data pages, and moving the data to the other filegroup. The table will be on the new filegroup.

NJ-DBA SSChampion Points: 13834 More actions · Answer 2

To list all the tables in a filegroup use:

select distinct(object_name(id)) from sysindexes

where groupid=filegroup_id('<filegroup-name>')

To list all indexes in a filegroup use:

select name from sysindexes

where groupid=filegroup_id('<filegroup-name>')

and indid > 0

where:

<filegroup-name>

is the name of the filegroup for which the list of tables or indexes is required.

for example, to list all the tables in a file group called "ConfigurationData":

select distinct(object_name(id)) from sysindexes

where groupid=filegroup_id('ConfigurationData')

anil702 Say Hey Kid Points: 670 More actions · Answer 3

Thanks for ur information.

without creating clustered index on filegroup we cant move the table having nonclustered index ?

-------------------------------

code for moving table haing clusterd index

alter table databasename.schema.tablename

drop constraint constraint_name

with (ONLINE = off, Move to filegroup_name)

and again creating clusteredindex

CREATE CLUSTERED INDEX index_name ON schema.tablename

ON filegroup

-------------------------------

code for moving nonclustered index ?

and in the above code what is ONLINE ----- OFF/ON

if We put ONLINE - ON in code it throws an error only for enterprise edition....

Can u explain for what sake we use this OFF/ON

if possible

sample code

NJ-DBA SSChampion Points: 13834 More actions · Answer 4

correct- can not move w/o clustered index.

Syntax for non-clustered index is same as clustered index, except instead of CLUSTERED use NONCLUSTERED. Of course, you want to drop the existing index first.

Online option allow the index to remain online during the rebuild process if you are doing a "reindexing operation". I've had some problems with this, so I typically dont use that option even w/ enterprise edition.

You can research these commands and options in Books Online.

Davekahn SSCommitted Points: 1840 More actions · Answer 5

Hi,

I have a similar situation and just need to clarify few thing, Instead of starting a new thread I'm using the old one, hopefully no minds 🙂

Following are the steps I have to take in order to move a table to a different file group:

1. create a File group (FG)

2. create a NDF with new File Group (second_FG) Path to new Drive

3. Drop Non cluster Indx

4. Drop F.Key ??

5. Drop Constraints If any?

6. Drop cluster Indx Option

ALTER TABLE dbo.mytablename

DROP CONSTRAINT cndx_PrimaryKey_mykey WITH (MOVE TO FG)

GO

7. ALTER TABLE dbo.mytablename

ADD CONSTRAINT cndx_PrimaryKey_mykey PRIMARY KEY CLUSTERED

(mycolumnname ASC) WITH

(IGNORE_DUP_KEY = OFF) ON FG

GO

I'm missing something? please confirm and help

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Please post new questions in a new thread. Thanks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Davekahn SSCommitted Points: 1840 More actions · Answer 7

Davekahn

SSCommitted

Points: 1840

December 8, 2011 at 12:28 pm

#1418913

Ok