1

I would like to partition a column by fiscalyear. How Do I create a partition function without harding coding values? This is what I currently have below.

create table dbo.CustomerAccount
(
    CustomerAccountId bigint primary key identity(1,1),
    CustomerId int not null,
    FiscalYear int,
    AccountBalance numeric(15,2)
)

How Do I create a partition function without harding coding values? 

CREATE PARTITION FUNCTION DateRangePF (datetime)
AS RANGE RIGHT FOR VALUES ('2015', '2016','2017')
Improve this question
1
  • If I understand you correctly you want to add partitions dynamically?
    – Tom V
    Mar 8, 2018 at 8:52

2 Answers 2

Reset to default
1

You cannot create a dynamic VALUES clause in a CREATE PARTITION FUNCTION statement. An alternative is to use the ALTER PARTITION FUNCTION statement to add a new range to the function.

I've encapsulated the required statements into a stored procedure:

IF OBJECT_ID(N'dbo.partition_function_add_date_range', N'P') IS NOT NULL
DROP PROCEDURE dbo.partition_function_add_date_range;
GO
CREATE PROCEDURE dbo.partition_function_add_date_range
(
      @function_name sysname
    , @range_date datetime
    , @scheme_name sysname
    , @next_filegroup sysname
)
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS (
        SELECT 1
        FROM sys.partition_functions pf
        INNER JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
        WHERE pf.name = @function_name
            AND prv.value = @range_date
        )
    BEGIN
        DECLARE @cmd nvarchar(max);
        SET @cmd = N'ALTER PARTITION SCHEME ' + QUOTENAME(@scheme_name) + N' NEXT USED ' + QUOTENAME(@next_filegroup) + N';';
        PRINT @cmd;
        EXEC sys.sp_executesql @cmd;
        SET @cmd = N'ALTER PARTITION FUNCTION ' + QUOTENAME(@function_name) + N'() SPLIT RANGE (''' + CONVERT(nvarchar(max), @range_date, 120) + N''');';
        PRINT @cmd;
        EXEC sys.sp_executesql @cmd;
    END
END
GO

An example of how to call this stored procedure:

EXEC dbo.partition_function_add_date_range @function_name = N'DateRangePF '
    , @range_date = '2018-01-01 00:00:00'
    , @scheme_name = N'DateRangeSC'
    , @next_filegroup = N'PRIMARY';

This could be added to a SQL Server Agent Job, scheduled to run once a year, prior to rows being inserted into the new fiscal year. The job step command would run something like this, which will automatically add the next year to the partition function, and any associated tables. 

DECLARE @daterange datetime;
SET @daterange = CONVERT(varchar(30), DATEPART(YEAR, GETDATE()) + 1) + '-01-01 00:00:00';
EXEC dbo.partition_function_add_date_range @function_name = N'DateRangePF'
    , @range_date = @daterange
    , @scheme_name = N'DateRangeSC'
    , @next_filegroup = N'PRIMARY';

It's important to realize partition function modifications will affect ALL tables that use the partition function.

Improve this answer
-1

I think something like this has already been asked https://stackoverflow.com/questions/18842764/how-to-dynamically-declare-partition-range-in-partition-function-in-sql-server

You could also look at interval partitioning https://sqlfascination.com/2010/09/12/interval-partitioning-in-sql-server-2008/

You could also create a job that created the partition function for you on a regular basis. https://social.technet.microsoft.com/Forums/sqlserver/en-US/d9f3cb78-a8c8-4bbb-8905-b02da2475a9f/dynamic-partition-creation-on-a-table-in-sql-server-2008

Improve this answer