Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1605

Re: Inadvertently moving table to a new filegroup? (2005, 2012)

Show all headers | View raw

The clustered index is the table data, so whichever filegroup it's created
on is where the data resides. I've dealt with needing to move data to
different filegroups in order to deal with disk space limitations, so I can
confirm that when the clustered index is created on a different filegroup,
that's where the data winds up, regardless of SQL Server version.

Here is a note from the CREATE INDEX article in SQL 2000 BOL:
Note  Because the leaf level of a clustered index and its data pages are the
same by definition, creating a clustered index and using the ON filegroup
clause effectively moves a table from the file on which the table was
created to the new filegroup. Before creating tables or indexes on specific
filegroups, verify which filegroups are available and that they have enough
empty space for the index. It is important that the filegroup have at least
1.2 times the space required for the entire table.

I've just verified the 2005, 2008 and 2012 articles contain the same note.

rja.carnegie@gmail.com wrote:
> I suppose I could try it and see what happens...
>
> Re http://technet.microsoft.com/en-us/library/ms175905.aspx
>
> My (mis?)understanding from Microsoft SQL Server 7.0 and/or 2000
> was that having created a table on one filegroup - say [PRIMARY] -
> a /clustered/ index cannot specify a different index filegroup -
> say [INDEX].  (This data centre uses lousy names.)  It has to
> be the same filegroup.
>
> But on SQL Server 2005 - planning to move soon to 2012 -
> colleagues, whom I'm intending to talk to about indexes
> pretty soon, appear to be allowed to create their clustered
> index and call it "ON [INDEX]".
>
> That Technet article, which is for 2012, says, "If a table has
> a clustered index, moving the clustered index to a new filegroup
> moves the table to that filegroup."
>
> There also is an article version for 2005 that doesn't say that.
>
> But, what should I expect to find where a table was newly
> indexed or newly created with a clustered index "ON [INDEX]"
> as filegroup?
>
> I would guess that (1) the table and index stay on [PRIMARY],
> ignoring the contrary clause; (2) the table and index are moved
> to [INDEX], which we didn't intend to do; or (3) sometimes
> 1 and sometimes 2, depending.
>
> Before I read the article, my preferred guess was,
> (4) the clustered index leaves are on [PRIMARY] - the table
> data pages - and the index nonleafs are on pages in [INDEX].
> But I think that the article points away from that.
>
> So, what /does/ it do?

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Inadvertently moving table to a new filegroup? (2005, 2012) rja.carnegie@gmail.com - 2013-11-04 07:03 -0800
  Re: Inadvertently moving table to a new filegroup? (2005, 2012) "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2013-11-04 12:52 -0500
  Re: Inadvertently moving table to a new filegroup? (2005, 2012) Erland Sommarskog <esquel@sommarskog.se> - 2013-11-04 22:49 +0100
    Re: Inadvertently moving table to a new filegroup? (2005, 2012) rja.carnegie@gmail.com - 2013-11-05 08:25 -0800
      Re: Inadvertently moving table to a new filegroup? (2005, 2012) "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2013-11-05 15:56 -0500
      Re: Inadvertently moving table to a new filegroup? (2005, 2012) Erland Sommarskog <esquel@sommarskog.se> - 2013-11-07 00:02 +0100

csiph-web