January 30, 2007 at 10:38 am
Hi, Group.
I have a SQL (below) that I want to use to create a table on the [tables] filegroup and a Primary Key on the [indexes] filegroup. When I run it though, the table ends up on the [indexes] filegroup as well. I've tried going through the commands synatx on BOL but I don't see anything wrong. Is what I am doing possible or do I need to use two separate SQL statements?
CREATE TABLE [test_table]
([col1] [BIGINT] CONSTRAINT [test_table_pk] PRIMARY KEY WITH fillfactor=75 ON [indexes],
[col2] [BIGINT],
[col3] [BIGINT])
ON [tables]
go
Cheers, Max
January 30, 2007 at 11:01 am
This is because by default the PK is the clustered index and hence the index IS the table. You will have to specify the PK as a nonclustered index for this to work.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
January 30, 2007 at 11:08 am
Thanks for the response!
Cheers, Max
January 31, 2007 at 12:09 pm
Creating nonclustered primary keys is the only way to put the primary key in a separate filegroup from the table, but that doesn't mean it is a good idea. I would recommend leaving the clustered primary keys in the [tables] filegroup except where you have determined a table would benefit from a clustered secondary index and nonclustered primary key, then you would create the key in [indexes] and the index in [tables].
January 31, 2007 at 12:20 pm
Thanks, Scott. That's what I did. I took all the "other" indexes and put then in the [indexes] group while leaving the PK in [tables].
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply