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

Groups > comp.databases.ms-sqlserver > #1604 > unrolled thread

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

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#1604 — Inadvertently moving table to a new filegroup? (2005, 2012)

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?

[toc] | [next] | [standalone]

#1605

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?

[toc] | [prev] | [next] | [standalone]

#1606

 (rja.carnegie@gmail.com) writes:
> But, what should I expect to find where a table was newly 
> indexed or newly created with a clustered index "ON [INDEX]"
> as filegroup?
 
In the [INDEX] filegroup.

There are no differences between SQL 2005 and SQL 2012 in this regard.

-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [next] | [standalone]

#1607

On Monday, 4 November 2013 21:49:45 UTC, Erland Sommarskog  wrote:
> (rja.carnegie@gmail.com) writes:
> 
> > But, what should I expect to find where a table was newly 
> > indexed or newly created with a clustered index "ON [INDEX]"
> > as filegroup?
> 
> In the [INDEX] filegroup.
> 
> There are no differences between SQL 2005 and SQL 2012
> in this regard.

Wow.  Was I supposed to know this?  :-(

So this script (as I see) puts the table in "INDEX" although
I specifically said "PRIMARY".

CREATE TABLE rjac20131105a 
    (
      i int
        NOT NULL
        CONSTRAINT rjac20131105b PRIMARY KEY ON [INDEX]
    , j int NULL
    )
ON [PRIMARY]
;

Thank you; I'm glad I got around to asking!

[toc] | [prev] | [next] | [standalone]

#1608

rja.carnegie@gmail.com wrote:
> On Monday, 4 November 2013 21:49:45 UTC, Erland Sommarskog  wrote:
>> (rja.carnegie@gmail.com) writes:
>>
>>> But, what should I expect to find where a table was newly
>>> indexed or newly created with a clustered index "ON [INDEX]"
>>> as filegroup?
>>
>> In the [INDEX] filegroup.
>>
>> There are no differences between SQL 2005 and SQL 2012
>> in this regard.
>
> Wow.  Was I supposed to know this?  :-(
>
> So this script (as I see) puts the table in "INDEX" although
> I specifically said "PRIMARY".
>
> CREATE TABLE rjac20131105a
>     (
>       i int
>         NOT NULL
>         CONSTRAINT rjac20131105b PRIMARY KEY ON [INDEX]
>     , j int NULL
>     )
> ON [PRIMARY]
> ;
>
> Thank you; I'm glad I got around to asking!

That's right. I just confirmed it by testing your script.

[toc] | [prev] | [next] | [standalone]

#1610

 (rja.carnegie@gmail.com) writes:
> So this script (as I see) puts the table in "INDEX" although
> I specifically said "PRIMARY".
> 
> CREATE TABLE rjac20131105a 
>     (
>       i int
>         NOT NULL
>         CONSTRAINT rjac20131105b PRIMARY KEY ON [INDEX]
>     , j int NULL
>     )
> ON [PRIMARY]
> ;
> 

Yes, but not that if the table has a LOB column, then the data for this
column (except anything that is saved in row), is placed on PRIMARY. Check this:

CREATE DATABASE rja ON (NAME = 'PRIMARY', FILENAME = 'C:\temp\rja.mdf'),
FILEGROUP [INDEX]  (NAME = 'INDEX', FILENAME = 'C:\temp\rja_index.ndf')
go
USE rja
go
CREATE TABLE rja (id int NOT NULL,
                  data char(24) NOT NULL,
                  blob nvarchar(MAX) NOT NULL,
                  CONSTRAINT pk PRIMARY KEY CLUSTERED(id) ON [INDEX]
)
ON [PRIMARY]
go
INSERT rja (id, data, blob)
  VALUES (1, 'Data', replicate(convert(nvarchar(MAX), 'blob'), 8000))
go
SELECT ds.name, ds.type, au.total_pages
FROM   sys.data_spaces ds
JOIN   sys.allocation_units au ON ds.data_space_id = au.data_space_id
JOIN   sys.partitions p ON au.container_id = p.hobt_id
WHERE  p.object_id = object_id('rja')
go
USE tempdb
go
DROP DATABASE rja

(You can move the LOB data elsewhere with the TEXTIMAGE_ON clause.)


-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web