Switched Off!

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s