John Davis John Davis - 5 months ago 9
SQL Question

How to dynamically declare PARTITION RANGE IN Partition function in sql server

I want to dynamically declare the range of my partition function.
I don't want to hard-code the range value,like below:


CREATE PARTITION FUNCTION PartFun(smallint)AS RANGE LEFT FOR VALUES (1,2,3,4,5)


The problem is:The column(ID in IDMASTER table) on which I want to create partition is of smallint datatype.

Declare @IDS NVARCHAR(100)

SELECT @IDS = coalesce(@IDS + ', ', '') + CAST(a.ID AS VARCHAR) FROM
(SELECT DISTINCT TOP 100 ID from IDMASTER ORDER BY ID ) a
--PRINT @IDS --it prints 0,1,2,3,4 like that

CREATE PARTITION FUNCTION PartFun(smallint)
AS RANGE LEFT FOR VALUES (@IDS)


Getting this error:


"Could not implicitly convert range values type specified at ordinal 1 to partition function parameter type."


Any workaround idea?
Also,is it that people mostly use hard coded values for partition range?

Raj Raj
Answer

May not be an exact solution to what you are looking for. Here is the scenario I am faced with:

We have a DB that has multiple tables partitioned on a column named 'PriceListDate', but strangely, the data type is Varchar(8). We are in the middle of redesigning the application and the DB, so decided to change the data type to 'Date'. Here is how we are doing this dynamically:

IF NOT EXISTS (SELECT NULL FROM sys.partition_functions WHERE name = N'PriceListDateFunction')
BEGIN;
    DECLARE @CreatePartitionFunctionScript NVARCHAR(MAX);

    SET @CreatePartitionFunctionScript =  'CREATE PARTITION FUNCTION [PriceListDateFunction] (Date) AS RANGE LEFT FOR VALUES (' +
                                            STUFF((SELECT ','+'N'+''''+CAST(prv.value as varchar(8))+'''' 
                                                        FROM sys.partition_range_values prv 
                                                        INNER JOIN sys.partition_functions pf 
                                                            ON pf.function_id = prv.function_id 
                                                        WHERE pf.name = 'PriceListFunction' 
                                                    FOR XML PATH(''), TYPE 
                                                    ).value('.', 'NVARCHAR(MAX)'),1,1,'') --Get list of existing partitons from existing partition function
                                                + ')';  
    --  Create Partition Function
    EXECUTE sp_executesql @CreatePartitionFunctionScript;
END;

Hope this gives you some ideas.

Raj

Comments