Using SQL 2008 R2 Enterprise
I am trying to partition a pair of existing 200 million row tables to improve reporting performance. As I understand it, I need to drop the existing clustered index and create a new one on the date stamp the partition funciton and scheme is based on. I don't want to dump the data into a new table because I want the existing reports to work without modification.
Here's my code:
CREATE PARTITION FUNCTION ByCreatedWhen_fn (DATETIME)
AS RANGE RIGHT FOR VALUES
CREATE PARTITION SCHEME ByCreatedWhen_scm
AS PARTITION ByCreatedWhen_fn
ALL TO (ESP); -- existing filegroup that contains the tables
ALTER TABLE [dbo].[SXEvent] DROP CONSTRAINT [SXEventPK]
CREATE CLUSTERED INDEX [ByCreatedWhen_idx] ON [dbo].[SXEvent]
([CreatedWhenUTC] ASC )
When I go to create the index I get the error:
Msg 2726, Level 16, State 1, Line 3
Partition function 'ByCreatedWhen_fn' uses 1 columns which does not match with the number of partition columns used to partition the table or index.
But I'm only using one column to partition the table & index. What does this mean?