November 3, 2009 at 10:10 am
Hi,
We've got a table with millions of records but only those with idStatus = 1 are shown on the public web site, all the other are only shown on the back office.
My idea was to create a partition on idStatus for that table.
I've got to create the partition function, partition schema, drop the existing clustered index and create it again.
Since we've got 2 Quad Core CPU with HT I decided to create a new filegroup (AdsStorage) and add a data file in that filegroup.
The idStatus can be from 1 to 6.
I'd like for the rows with idStatus = 1 to stay in the PRIMARY filegroup and all the other to move to AdsStorage.
I was planing on this:
CREATE PARTITION FUNCTION fn_AdsStorage (int)
AS RANGE LEFT FOR VALUES (1);
GO
CREATE PARTITION SCHEME schm_AdsStorage
AS PARTITION fn_AdsStorage
TO (PRIMARY, AdsStorage);
GO
ALTER TABLE AdsMain DROP CONSTRAINT pk_AdsMain
GO
but PRIMARY is not allowed.. Do I have to create another filegroup (AdsActive) or can I, somehow, use the PRIMARY filegroup?
And since my PRIMARY KEY isn't the column on my partition function I'll have to create a CLUSTED INDEX on the idStatus column using the function, right? But is there any way to tell SQL to, within the partitions, to use the primary key to sort the rows?
Thanks,
Pedro
November 3, 2009 at 11:42 am
Got over the PRIMARY problem... has to be [PRIMARY]...
As to the other questions ... still holding...
Pedro
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply