How to Convert to a Paritioned Table

Question

Post reply

How to Convert to a Paritioned Table

ahmed_b72

Ten Centuries

Points: 1251
More actions
September 13, 2011 at 9:04 pm

#390399

I've got a problem in converting a table into a partitioned table.

I have a table of the following simple structure:

[font="Courier New"]CREATE TABLE dbo.orders (

OrderID int identity(1,1),

OrderDate datetime NOT NULL,

OrderAmount money NOT NULL

CONSTRAINT pk_orders PRIMARY KEY CLUSTERED (OrderDate,OrderID))[/font]

I'd like to convert it into a partitioned table by its ORDERDATE column.

I've succesffully create the partition funcation and scheme.

The following link:

http://msdn.microsoft.com/en-us/library/ms175864.aspx

suggests using CREATE INDEX, so I did the following:

[font="Courier New"]

CREATE clustered index pk_orders ON [dbo].[orders]([OrderDate] ASC, [OrderID])

WITH (DROP_EXISTING = ON )

ON partscheme(OrderDate)[/font]

I've got the following error:

[font="Courier New"]Msg 1907, Level 16, State 1, Line 2

Cannot recreate index 'pk_orders'. The new index definition does not match the constraint being enforced by the existing index.[/font]

How can I convert the index clustered table into a partitioned table?

SQL Server 2008 on Windows 2003 Server

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

HowardW One Orange Chip Points: 29109 More actions · Answer 1

I'm no expert on partitioning, but there's a difference between a clustered index and a Primary Key. I don't believe you can use DROP_EXISTING to drop a clustered PK (which is a constraint), then rebuild as a non-unique clustered index on a partitioning scheme.

I don't have an environment I can test on at the moment, but I assume you'd have to recreate your PK constraint with the partitioning scheme.

I've also got a feeling you can do this more efficiently by creating a new table with the partitioning scheme and then use the ALTER TABLE...SWITCH syntax and rename the table afterwards, but again, I'm not familiar enough with it to guide you through it.