We have a large data warehouse that has a few partitioned tables. Having only arrived at this job in the last few weeks, I decided that I needed to learn a bit more than just the theory of partitioning. Armed with Kendra Little’s partitioning scripts I decided to set up a test harness to see what I could do and how I could break it.
After some modifications to Kendra’s scripts I decided to run my statements.
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table ‘%.*ls’ allows values that are not allowed by check constraints or partition function on target table ‘%.*ls’.
It turns out that breaking things wouldn’t be too difficult. Some searching on Bingle led to what I hoped would be a good post but after trying it, it didn’t work. It was a pretty straightforward solution that was caused by trying to switch in a table that allowed NULL values while the original, partitioned table did not allow NULL values. So it was back to the drawing board for me.
Interestingly, the clue to the resolution to my problem was found within the script itself. The original script that I was trying to run looked something like below:
CREATE TABLE pt_test ( pt_id int IDENTITY NOT NULL, pt_date DATETIME2(0) NOT NULL, pt_code nvarchar(2) NOT NULL, pt_value INTEGER ) on ps_test_partition_scheme(pt_date); GO INSERT pt_test (pt_date, pt_code, pt_value) SELECT pt_date = DATEADD(SECOND, t.N, CAST('20130401' AS DATETIME2(0))), pt_code = CASE WHEN t.N BETWEEN 48 AND 122 OR t.N BETWEEN 192 AND 687 THEN NCHAR(t.N) ELSE N'AA' END, pt_value = t.N FROM ph.tally t; <pre>
I repeated this insert across a total of four partitions. I then dutifully created my primary key
ALTER TABLE pt_test ADD CONSTRAINT pk_pt_test PRIMARY KEY CLUSTERED(pt_date,pt_id) GO
After adding my next partition and filegroup, I created my staging table:
CREATE TABLE pt_test_load ( pt_id int IDENTITY NOT NULL, pt_date DATETIME2(0) NOT NULL, pt_code nvarchar(2) NOT NULL, pt_value INTEGER ) on pf_filegroup_6 ; GO
I added my constraints, along with the constraints to ensure that everything fell within the boundaries of the new partition:
ALTER TABLE pt_test_load ADD CONSTRAINT pk_pt_test_load PRIMARY KEY CLUSTERED(pt_date,pt_id); GO ALTER TABLE pt_test_load WITH CHECK ADD CONSTRAINT CKpt_test_load_bottom CHECK (pt_date >= '20130801'); ALTER TABLE pt_test_load WITH CHECK ADD CONSTRAINT CKpt_test_load_top CHECK (pt_date < '20130901'); ALTER PARTITION SCHEME ps_test_partition_scheme NEXT USED pf_filegroup_7; GO
But when I went to switch the data in, I got the error.
Did you catch my mistake?
After a few days I went back and re-read Kendra’s scripts and noticed a question that she put in it:
--Create a staging table on our new filegroup (dailyFG6) --Why are we seeding the identity here? --What would happen if we didn't?
In my haste, I’d forgotten to seed my identity. That was the value that was not allowed. It had nothing to do with the values that my partitioning function was looking at.
So I seeded my identity in my staging table
CREATE TABLE pt_test_load ( pt_id int IDENTITY(5000001,1) NOT NULL, pt_date DATETIME2(0) NOT NULL, pt_code nvarchar(2) NOT NULL, pt_value INTEGER ) on pf_filegroup_6 ; GO
… and switched in without an error.