lundi 4 juillet 2016

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?

Aucun commentaire:

Enregistrer un commentaire