CODEWITHSUNDEEP
sqlsql-serverpartitioning
John Davis
John Davis

Reputation: 43

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?

Upvotes: 2

Views: 15436

Answers (4)

SKARVA Bodavula
SKARVA Bodavula

Reputation: 957

--Create date partition function with increment by month.

DECLARE @DatePartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime) AS RANGE RIGHT FOR VALUES (';
DECLARE @i datetime = '20190101';
WHILE @i < '20221231'
BEGIN

SET @DatePartitionFunction += '''' + CONVERT(CHAR(8),@i,112) + '''' + N', ';
SET @i = DATEADD(MM, 1, CONVERT(CHAR(8),@i,112));
END
SET @DatePartitionFunction += '''' + CONVERT(CHAR(8),@i,112)+ '''' + N');';
EXEC sp_executesql @DatePartitionFunction --print @DatePartitionFunction;
GO

Upvotes: 1

Arpit Saxena
Arpit Saxena

Reputation: 39

Well what you can do is this:

Use QuoteName(exp,'''')

While creating your string of values (in this case ids) and the rest remains the same. Then execute it normally and it works great.

Upvotes: 0

John Davis
John Davis

Reputation: 43

Thanks, got idea. This is how I have solved it:

DECLARE @sqlcmd nvarchar(400),@ids nvarchar(100);
SET @sqlcmd = N'CREATE PARTITION FUNCTION PartFun(smallint) AS RANGE LEFT FOR VALUES (' + @ids + N')' ;
--PRINT @sqlcmd
EXEC SP_EXECUTESQL @sqlcmd

So the solution is:Dynamic SQL and making everything as NVARCHAR!

Upvotes: 0

Raj
Raj

Reputation: 10843

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

Upvotes: 2

Related Questions