Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  Database Design and Application Architecture  Issue with creating partitioned tables

Author  Topic  kcanniff Starting Member 1 Post Posted - 2008-12-03 : 17:44:29 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