Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 How to Convert to a Paritioned Table

Author  Topic 

ozSQLServer
Starting Member

32 Posts

Posted - 2011-09-13 : 23:01:57

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

I have a table of the following simple structure:
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))
/

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:

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


I've got the following error:
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.


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

SQL Server 2008 on Windows 2003 Server

Cheers,
ozSQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 23:48:05
is the existing index clustered? if not, drop it and then create

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -