Raihan U Ahmed Raihan U Ahmed - 3 months ago 9
SQL Question

Uses of SUBSTRING in SQL Partitioning

I have created a table and partitioned it(the code is here, Please see the Example of horizontal partitioning with creating a new partitioned table part)
I have use the following Partition function:

CREATE PARTITION FUNCTION [PartitioningByMonth] (datetime)
AS RANGE RIGHT FOR VALUES ('20140201', '20140301', '20140401',
'20140501', '20140601', '20140701', '20140801',
'20140901', '20141001', '20141101', '20141201');


And my SCHEMA is :

CREATE PARTITION SCHEME PartitionBymonth
AS PARTITION PartitioningBymonth
TO (January, February, March,
April, May, June, July,
Avgust, September, October,
November, December);


And My table create Query is :

CREATE TABLE Reports
(ReportDate datetime PRIMARY KEY,
MonthlyReport varchar(max))
ON PartitionBymonth (ReportDate);
GO


if i insert values which are in the year of
2014
(i mean
20140105
,
20140205
, ...... ), it works fine :

INSERT INTO Reports (ReportDate,MonthlyReport)
SELECT '20140105', 'ReportJanuary' UNION ALL
SELECT '20140205', 'ReportFebryary' UNION ALL
SELECT '20140308', 'ReportMarch' UNION ALL


But problem occurs when i insert any value of
2015
,
2016
,
2017
.....:

INSERT INTO Reports (ReportDate,MonthlyReport)
SELECT '20150105', 'ReportJanuary' UNION ALL
SELECT '20150206', 'ReportFebryary' UNION ALL
SELECT '20150309', 'ReportMarch' UNION ALL


as it push it only the last partition (
20141201
i mean
December
partition). But i want them to go
January
,
February
,
March
partition.
I know my problem is in
PARTITION FUNCTION
because i wrote ('20140201', '20140301',......) only for 2014 year.
But i want to count the Month only .Is there any way so that it only see the month only 20150301 rather than year or day.(can i use SUBSTRING in any part )

Answer

you can add a computed column to your table as

ReportDateP as MONTH(ReportDate) PERSISTED

and create the table with partition on this computed column

Then create the partition function as follows

CREATE PARTITION FUNCTION [NewPartitioning] (int)
AS RANGE RIGHT FOR VALUES (2,3,4,5,6,7,8,9,10,11,12);