SQL Server Performance Forum – Threads Archive
Table Partitioning with Existing Table
I’ve been trying to implement the Table Partitioning feature of SQL Server 2005, however I’d like to do it by using an existing table. So far I’ve done the following: 1. Created Files and specified their Filegroups.2. Created Partition Function specifying it would apply to DateTime type, and specified Ranged of values.
3. Created Partition Scheme referencing my partition function. The last thing for me to do is have the actual connection from the column of my table to my partition scheme. Most of the examples I’ve seen such as this article on BOL http://msdn2.microsoft.com/en-us/library/ms345146.aspx#sql2k5parti_topic8) were creating new tables. In this article http://www.databasejournal.com/features/mssql/article.php/3647561)
they move the table to a new partition by creating a clustered index. I read that it’ll work that way at another location as well. The statement they used was Drop index MyTable_IXC on MyTable with (Move To [Data Partition Scheme] (ID) ) However, this format doesn’t seem to work for me. I receive the following error: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index ‘STAY.STAYPRIMARY’. It is being used for PRIMARY KEY constraint enforcement. AS you can see, my primary key and clustered index are the same for this table. Any ideas on how I can get this table partitioned with the data in it?
I believe the error is self-explanatory and you cannot drop if the key is alredy linked as a constraitint, removing a primary key constraint from a table is a redesign of the table, and requires careful thought. It makes sense to know the only way to achieve this task is to either drop the table and use a CREATE TABLE command to recreate the table without the index, or to use the ALTER TABLE command. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
I tried using the ALTER table method and managed to get some success. However, there’s some fundamental changes in the back-end now. The steps I took are as below: 1. Drop Constraint for Primary Key
2. Drop Index for Date column
3. Alter Date column to make it NOT NULL
4. Alter Table Re-adding Primary Key constraint with the former primary key field and the date field. So as you can see, the 2 major changes I have are the Clustered Index now consists of 2 fields (an Id field, and the date field). Also, the date column is now set to have to take NOT NULL. This should work out for this setup. Thanks for the suggestion satya.
]]>