I'm doing work with table partitioning to improve performance and have run into different results with 2 different approaches, and wanted to see if anyone else has run into the same situation. The issue is that when partitioning a new table, SQL forces me to include the partitioning column (a date column) in the Primary Key constraint (previously the PK was an Identity INT column). However, when I implemented partitioning on an existing table with the same structure by creating the clustered index on the new partition scheme, the server allowed me to do it. And when I checked the tables using the $partition function after loading data in them, they looked identical (i.e. both tables had identicial data in the correct partitions). My questions are: * Will the serverâ€™s behavior when loading to and reading from these 2 tables differ, because the primary key constraint on the 2nd (pre-existing) table is missing the partition column? * Is the server allowing me to do something with method 2 that it really shouldnâ€™t, or is the error with method 1 not legitimate. * Also â€“ I'm testing with SQL 2005 - does the same condition exist in SQL 2008 (thatâ€™s my next testing step). Hereâ€™s the specific code: -- Method 1 -- Create the Partition objects CREATE PARTITION FUNCTION Table1_Submit_Date_PF (DATETIME) AS RANGE RIGHT FOR VALUES ( '20080101 00:00:00.000', '20081001 00:00:00.000', '20081101 00:00:00.000', '20081201 00:00:00.000' ) GO CREATE PARTITION SCHEME Table1_Submit_Date_PS AS PARTITION Table1_Submit_Date_PF ALL TO ([PRIMARY]) GO -- Create the Table CREATE TABLE Table1 ( Row_Key INT IDENTITY (1, 1) NOT NULL, Account_Number VARCHAR (24), Submit_Date DATETIME, CONSTRAINT PK_Table1 PRIMARY KEY NONCLUSTERED (Row_Key) ) ON Table1_Submit_Date_PS (Submit_Date) GO -- Resulting error: Msg 1908, Level 16, State 1, Line 1 Column 'Submit_Date' is partitioning column of the index 'PK_Table1'. Partition columns for a unique index must be a subset of the index key. After researching, I found that the PK needed to include Submit_Date (which I can do, but will then have to modify tables with Foreign Keys to this table). So I tried the alternative approach first of creating the partition structure by placing the clustered index for the table on the partition scheme (since thatâ€™s what Iâ€™ll end up doing anyway): -- Method 2 -- Create the table with single key PK CREATE TABLE Table2 ( Row_Key INT IDENTITY (1, 1) NOT NULL, Account_Number VARCHAR (24), Submit_Date DATETIME, CONSTRAINT PK_Table1 PRIMARY KEY NONCLUSTERED (Row_Key) ) ON [PRIMARY] GO -- Create the partitions CREATE PARTITION FUNCTION Table2_Submit_Date_PF (DATETIME) AS RANGE RIGHT FOR VALUES ( '20080101 00:00:00.000', '20081001 00:00:00.000', '20081101 00:00:00.000', '20081201 00:00:00.000' ) GO CREATE PARTITION SCHEME Table2_Submit_Date_PS AS PARTITION Table1_Submit_Date_PF ALL TO ([PRIMARY]) GO -- Partition the table by creating the clustered index on the partition CREATE CLUSTERED INDEX cdxTable2_SubmitDate ON Table2 (Submit_Date DESC) ON Table2_Submit_Date_PS(Submit_Date) GO The server allowed me to do this. And after loading data into both tables (Table2 already had data) and checking with the $partition function. They looked identical.