Alter Table Partitioning function




    I have a big table which is partitioned  , I need to change the portioning  function (including the  column used by partition key). I don’t want to change the table or use a temp table as there are a lot of dependent objects to this table


    I wrote:


    DECLARE @partition_count INT,@cmd VARCHAR(8000)

    SELECT @partition_count=MAX(p.partition_Number) FROM sys.allocation_units A,sys.partitions  P ,sys.data_spaces D


    AND  A.container_id=P.Partition_id

    AND A.data_space_id=D.data_space_id

    SELECT @partition_count


    WHILE (@partition_count>1)


                SELECT @cmd='ALTER PARTITION FUNCTION MainPartitionFunction() MERGE range ('+CONVERT(VARCHAR(10),@partition_count-1)+')'

                exec  (@cmd)

                SELECT @partition_count=@partition_count-1



    Which will merge all the existing partitions to one. But I don’t seem to be able to find a way  change  the existing funtion. ( in need to change the partition key all together)

    Is there a way to disable or drop parition of the table all together (convert the table to a non-partitioned table) and then I can re define the parition  function again.

    Any ideas?


    Thursday, March 27, 2008 12:37 PM

All replies

  • how many partitions do you have?

    are they all the same? ( are some ready only for example )

    Saturday, March 29, 2008 8:29 PM
  • Have you tried to modify the table in SSMS?

    Right click the tableàmodifyàRegular Data Space Specification, and change the “File Group or Partition Scheme Name” to the new partition scheme and then set the partition column.

    But I am not sure if it will affect the existing data; maybe you can do a test firstJ

    Sunday, March 30, 2008 3:14 PM