May 16, 2008 at 2:55 am
Hi,
I'm reading though the partitioned tables section of the books online, but i can't figure out if it is possible to make an already exisitng table partitionned. We have big table at the client that we need to partition, and if possible, we would like to avoid swapping all the data from table to table.
Any idea if i can ALTER the table to use the scheme/function? I'm starting to suspect this isn't possible but i hope somebody can prove me wrong 🙂
May 16, 2008 at 4:34 am
Hi,
You cannot partition the existing table using ALTER TABLE statement. But if you want to create the partition of the existing table then follow these steps:
- Rename your existing table.
- Create new partition table with the same name as old unpartition table
- Use DTS or SSIS to copy the records from one table to another and deleted old table
- Delete the old table.
Hope this helps in your situation...
Basit Ali Farooq
MCITP Database Administrator
Microsoft Certified Professional Developer (Web Applications)
Microsoft Certified Database Administrator
Microsoft Certified Systems Engineer
Microsoft Certified Systems Administrator
CIW Security Analyst
Cisco Certified Network Associate
May 16, 2008 at 5:14 am
Well, since the volume of data is so large, i want to avoid copying any data. I found something in the help that might ... eh .... help :
ALTER TABLE SWITCH
If i understood correctly i can rename the table, re-create partitionned version of the table ( meaning, linked to scheme, not necessarily partionned into several partitions yet ), and use the SWITCH syntax to swap the data of the renamed table with partition 1.
I'll let you know if that worked ( if you don't hear from me again that means the server exploded )
May 16, 2008 at 7:45 am
I have setup the function, scheme and new partitionned table, i removed all index/primary keys for convenience.
But when i try to execute this statement :
ALTER TABLE TheTable_Unpartitioned SWITCH
TO TheTable_Partitioned PARTITION 2
I get this error :
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table '...' allows values that are not allowed by check constraints or partition function on target table '...'.
The sql books online clearly state that i have to create a constraint on the source table, and so i did, but this makes no difference. Also when i execute this query :
SELECT distinct $PARTITION.PartitioningFunction_SomeTable(SomeTable_Unpartitioned.TheDate) from SomeTable_Unpartitioned
It returns one row, with value 2, so i'm absolutely sure all data in the source table will go to partition 2.
I think i'm ready to eat my keyboard now ...
May 16, 2008 at 8:00 am
Found it ... quite stupid actually. The column i was using as partitioning key was set to nullable. Apparently that caused those problems ( even if the table contained to NULL values at all for that column )
I'll stop talking to myself now 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply