partitioning a table on the basis of last 2 digits of a column (computed column)

• Sachin Vaidya

  Ten Centuries

  Points: 1273

  More actions

  September 29, 2011 at 4:14 am

  #98143

  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

• Sachin Vaidya

  Ten Centuries

  Points: 1273

  More actions

  September 29, 2011 at 7:56 am

  #1388379

  Hi,

  I figured out my mistake. I have declared the data type as varchar in the partition function but I was passing int type.

  I should have used single quotes like this while defining partition function.

  CREATE PARTITION FUNCTION [PFN_Partition](varchar(2)) AS

  RANGE LEFT FOR VALUES ('00','01')

  Regards.