Create partition on a table with difference in partition key and primary key

• roshanmaugustine

  SSC Enthusiast

  Points: 144

  More actions

  November 16, 2010 at 5:09 am

  #226642

  Hi All,

  Can we create a partition on a table which is having primary key different and partition key different? Because in one of my table Id is primary key and I have to do a partition on date wise if possible how we can, I am new to partition

  Please find the below code which i was trying to create a partitions

  I have three different File groups [PRIMARY], FG2, FG3

  CREATE PARTITION FUNCTION salesYearPartitions (datetime) AS RANGE RIGHT FOR VALUES ( '2009-01-01', '2010-01-01')

  GO

  CREATE PARTITION SCHEME Test_PartitionScheme AS PARTITION salesYearPartitions TO ([PRIMARY], FG2, FG3 )

  GO

  CREATE TABLE SalesArchival

  (SalesId int identity PRIMARY KEY nonclustered,

  SaleTime datetime ,

  ItemName varchar(50))

  ON Test_PartitionScheme (SaleTime);

  GO

  I am getting an error

  Msg 1908, Level 16, State 1, Line 1

  Column 'SaleTime' is partitioning column of the index 'PK__SalesArchival__1367E606'. Partition columns for a unique index must be a subset of the index key.

  Msg 1750, Level 16, State 0, Line 1

  Could not create constraint. See previous errors.

• nigelrivett

  SSCertifiable

  Points: 5472

  More actions

  November 16, 2010 at 6:25 am

  #1251282

  No.

  The message is self explanatory. The partitioning columns must be included in unique indexes.

  see

  http://www.simple-talk.com/sql/database-administration/partitioned-tables-in-sql-server-2005/

  
  Cursors never.
  DTS - only when needed and never to control.