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

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 245829 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 12/15/2006 7:50:52 AM
Access Restriction Public

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
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 navogel 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.