SQL Server Filegroups
-
Hi, I am trying to set up my database with the table storage and the index storage in seperate filegroups. To do this I am trying the following SQL : CREATE TABLE ClosingArea ( ClosingArea_ID INTEGER NOT NULL, ClosingArea_Name VARCHAR(64) ) ON MM32TABLES; ALTER TABLE ClosingArea ADD CONSTRAINT CLOSINGAREA_PK PRIMARY KEY (ClosingArea_ID) ON MM32INDEXES; However this initially creates the table in the MM32TABLES filegroup but the "ALTER TABLE" statement changes the table storage to MM32INDEXES when I obviously only want to store the index in that filegroup. Any ideas why this is happening or how I can do what I want ? thanks in advance.
-
Hi, I am trying to set up my database with the table storage and the index storage in seperate filegroups. To do this I am trying the following SQL : CREATE TABLE ClosingArea ( ClosingArea_ID INTEGER NOT NULL, ClosingArea_Name VARCHAR(64) ) ON MM32TABLES; ALTER TABLE ClosingArea ADD CONSTRAINT CLOSINGAREA_PK PRIMARY KEY (ClosingArea_ID) ON MM32INDEXES; However this initially creates the table in the MM32TABLES filegroup but the "ALTER TABLE" statement changes the table storage to MM32INDEXES when I obviously only want to store the index in that filegroup. Any ideas why this is happening or how I can do what I want ? thanks in advance.
This is because the table and primary key (or clustered index) have to be on the same filegroup. You can create a table on the MM32TABLES group and then create a non-clustered index on MM32INDEXES. Jason Cono, MCSD