Home Dashboard Directory Help
Search

CREATE TABLE with PRIMARY KEY clause creates table on the wrong filegroup by Theanderblast


Status: 

Closed
 as By Design Help for as By Design


1
0
Sign in
to vote
Type: Bug
ID: 245829
Opened: 12/15/2006 7:50:52 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

This syntax (originally generated by SQL Server Management Studio's "Script Table as" feature, create the table FROG on the INDEXFILEGROUP not the STRUCTUREFILEGROUP as the syntax seems to imply:

CREATE TABLE [MYSCHEMA].[FROG](
    [SEQID] [int] IDENTITY(1,1) NOT NULL,
    [SEQVAL] [char](1),
PRIMARY KEY CLUSTERED
(
    [SEQID] ASC
) ON INDEXFILEGROUP
) ON STRUCTUREFILEGROUP
Details
Sign in to post a comment.
Posted by Microsoft on 12/21/2006 at 10:28 AM
Hi Theanderblast,
When a clustered index is created on a table then SQL Server moves the entire contents of the table into the same filegroup as the clustered index.
In this case

CREATE TABLE [MYSCHEMA].[FROG](
             [SEQID] [int] IDENTITY(1,1) NOT NULL,
             [SEQVAL] [char](1),
PRIMARY KEY CLUSTERED
(
             [SEQID] ASC
) ON INDEXFILEGROUP
) ON STRUCTUREFILEGROUP

The clustered index is on INDEXFILEGROUP therefore the table FROG is on the INDEXFILEGROUP not the STRUCTUREFILEGROUP.

(Only one clustered index is supported for a table so if you delete the existing clustered index and create a new one SQL Server moves the entire contents of the table into the same filegroup as the clustered index.)

Thanks
Arpita
Posted by Razvan Socol on 12/19/2006 at 12:09 PM
To clarify David's comment, for a table that has a clustered index, the clustered index contains all the data for that table, by definition. So the last ON clause is indeed redundant in this case.
Posted by David Portas on 12/16/2006 at 3:39 AM
When creating a table with a clustered index the ON clause for the table is arguably redundant. I think it would add confusion if clustered indexes used the table ON clause rather than the index ON clause. It would also break a lot of existing code. A better option might be to deprecate the table ON clause when a clustered index is in use.
Sign in to post a workaround.