如何在sql server中动态声明PARTITION RANGE IN Partition函数 [英] 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:

创建分区函数 PartFun(smallint)AS RANGE LEFT FOR VALUES (1,2,3,4,5)

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

问题是:我想在其上创建分区的列(IDMASTER 表中的 ID)是 smallint 数据类型.

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)

出现此错误:

无法将序号 1 指定的范围值类型隐式转换为分区函数参数类型."

"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?

推荐答案

可能不是您正在寻找的确切解决方案.这是我面临的场景:

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

我们有一个数据库，其中有多个表分区在名为PriceListDate"的列上，但奇怪的是，数据类型是 Varchar(8).我们正在重新设计应用程序和数据库，因此决定将数据类型更改为日期".以下是我们如何动态执行此操作:

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.

拉杰

这篇关于如何在sql server中动态声明PARTITION RANGE IN Partition函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！