x

Clustered index not created on the filegroup

I set up this table:

CREATE TABLE [dbo].[MyTable](
[MyID] [int] IDENTITY(1,69) NOT NULL,
[MyChar] [char](100) NULL,
[DateCreated] [datetime] NOT NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[MyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG1]
) ON [FG2]

GO

However both the table and index were on Filegroup FG1. Why?

more ▼

asked Oct 19, 2009 at 10:54 AM in Default

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 77 79 82

(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

Since the clustered index is the table within SQL Server, where you specify storage of the clustered index overrides where you specified storage of the table itself. This would also come into play if you created the table without the clustered index and then added the clustered index with the storage specification. It would "move" the table to where the cluster is stored since the cluster is, in fact, the table itself.

more ▼

answered Oct 20, 2009 at 08:42 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98k 19 21 74

(comments are locked)
10|1200 characters needed characters left

Because when you specify a filegroup for a table, you are specifying where the clustered index for that table goes, or where the heap goes if you have not specified a clustered index.

The specification at the table level takes precedence.

Also interestingly, if you had created the table without the constraint, then created the constraint, you would find that when you re-scripted it, it would claim that the ON clause for the table was FG1.

Edit->

So, if you did this:

CREATE TABLE [dbo].[MyTable](
[MyID] [int] IDENTITY(1,69) NOT NULL,
[MyChar] [char](100) NULL,
[DateCreated] [datetime] NOT NULL
) ON [FG2]

ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[MyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG1]

GO

Then when you scripted the table out, the script would be this:

CREATE TABLE [dbo].[MyTable](
[MyID] [int] IDENTITY(1,69) NOT NULL,
[MyChar] [char](100) NULL,
[DateCreated] [datetime] NOT NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[MyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG1]
) ON [FG1]

GO

Notice how now the table-level filegroup specification is FG1 - because that's where the clustered index resides. As far as SQL Server meta-data is concerned, there is no such thing as a table-level filegroup for main data, simply a filegroup which contains either the clustered index or heap.

more ▼

answered Oct 19, 2009 at 12:32 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

The table level is FG2. The index is FG1.
Oct 19, 2009 at 12:44 PM Steve Jones - Editor ♦♦
Yes... Are you referring to the last bit? I will edit my post with some code.
Oct 19, 2009 at 12:54 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x984
x126
x107

asked: Oct 19, 2009 at 10:54 AM

Seen: 3037 times

Last Updated: Oct 27, 2009 at 05:58 PM