Hello,
I want to partition a table on the basis of last 2 digits of ID column such that there will be 100 partitions like 00, 01, 02.....99 and each of them having rows with ID values like 2400, 3400, 5400 will go in partition 00 and 2101, 2201, 3301 will go in partition 01 and so on.
I have created the code like this, and for example purpose I am creating only 3 partitions here. After inserting the values when I check in sys.partitions I see that data is getting populated in only one partition. Please let me know how to implement it correctly.
CREATE PARTITION FUNCTION [PFN_Partition](varchar(2)) AS
RANGE LEFT FOR VALUES (00,01)
CREATE PARTITION SCHEME [PS_PartitionRange]
AS PARTITION [PFN_Partition]
TO ([PRIMARY],[PRIMARY],[PRIMARY])
CREATE TABLE PartitionedTable
(
ID INT
,DailySummaryDate DATETIME
,IDPartitionDigit AS RIGHT(ID, 2) PERSISTED
)
ON [PartitionRange](IDPartitionDigit)
;
GO
INSERT into PartitionedTable values (1200, 898, getdate())
INSERT into PartitionedTable values (1300, 898, getdate())
INSERT into PartitionedTable values (1201, 898, getdate())
INSERT into PartitionedTable values (1401, 898, getdate())
INSERT into PartitionedTable values (1501, 898, getdate())
INSERT into PartitionedTable values (1202, 898, getdate())
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='PartitionedTable';
GO