July 22, 2010 at 3:23 pm
Greetings all.
I have a massive table I am extracting into a staging database in sql server 2008 (windows 2003 both 64 bit). As a test I am doing 3 loads from the source into 3 separate tables that I use the partition switch to the final table from the 3 smaller tables. So far this has worked fine. What I am having issues with is switching the 2nd and 3rd table over. The 1st table works just fine.
Here is the layout
Staging.order_part (testing name) is the main table that is build on the partition function/scheme
the 3 tables that are loaded from the source are
zprt.order_1
zprt.order_2
zprt.order_3
Here is the script that builds the partition function and scheme.
CREATE PARTITION FUNCTION PF(INT)
AS RANGE LEFT
FOR VALUES (20081231,20091231,20101231 )
GO
CREATE PARTITION SCHEME PS
AS PARTITION PF ALL TO ([FG2-2])
Now it is my understanding that this would create 4 partitions based on these ranges
PARTITION 1 <= 20081231
PARTITION 2 > 20081231 AND <= 20091231
PARTITION 3 > 20091231 AND <=20101231
PARTITION 4 > 20101231
So I built the table constraints on the 3 load tables as such
ALTER TABLE zprt.order_1 ADD CHECK (OBLMDT <= 20081231);
ALTER TABLE zprt.order_2 ADD CHECK (OBLMDT > 20081231 AND OBLMDT <= 20091231);
ALTER TABLE zprt.order_3 ADD CHECK (OBLMDT > 20091231 AND OBLMDT <= 20101231 );
And issue the following commands
ALTER TABLE zprt.order_1 SWITCH TO staging.order_part partition 1
ALTER TABLE zprt.order_2 SWITCH TO staging.order_part partition 2
ALTER TABLE zprt.order_3 SWITCH TO staging.order_part partition 3
The first one completes fine however the second and third give me the following error;
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'Zprt.order_2'
allows values that are not allowed by check constraints or partition function on target table 'staging.order_part'.
To validate I ran the following scripts and their following results are after them
select min(oblmdt), max(oblmdt) from zprt.order_1 = 0 to 20081231 -- this table switch works
select min(oblmdt), max(oblmdt) from zprt.order_2 = 20090102 to 20091231
select min(oblmdt), max(oblmdt) from zprt.order_3 = 20100104 to 20100722
As you can see, the ranges of each table fall within the partition function's ranges as well as the check constraints. The check constraints and partition function's parameters match up, so I am at a complete loss as to why I am getting this error.
Any ideas?
Link to my blog http://notyelf.com/
July 22, 2010 at 10:58 pm
This is very strange,
So to prove it's not the tables themselves, I have utilized zprt.order_2 and zprt.order_3 with the original data to switch them over (as if they were both table 1) and this worked fine.
I recreated the partition function with values as (1,2,3)
I then updated the columns of each table so that table 1 oblmdt = 1 table 2 oblmdt = 2 and table 3 oblmdt = 3. I then removed the old constraints and added the new ones logical for this and yield the same errors.
I have created test script that works perfectly fine, so I am not sure what the difference is as to why this is failing
Here is code that proves my method
DROP PARTITION FUNCTION PF1
CREATE PARTITION FUNCTION PF1 (INT)
AS RANGE LEFT
FOR VALUES (1,2)
DROP PARTITION SCHEME PS1
CREATE PARTITION SCHEME PS1
AS PARTITION PF1 ALL TO ([FG2-2])
DROP TABLE zprt.final
CREATE TABLE zprt.final
(ID int not null
,data nvarchar(max)) on ps1 (ID)
drop table zprt.tbl1
CREATE TABLE zprt.tbl1
(ID int not null,data nvarchar(max)) on [fg2-2]
drop table zprt.tbl2
CREATE TABLE zprt.tbl2
(ID int not null,data nvarchar(max)) on [fg2-2]
drop table zprt.tbl3
CREATE TABLE zprt.tbl3
(ID int not null,data nvarchar(max)) on [fg2-2]
INSERT INTO zprt.tbl1
Values (1,'assfdsf')
INSERT INTO zprt.tbl2
Values (2,'asdfasdfasdfad')
INSERT INTO zprt.tbl3
Values (3,'asdfasdfasdfasdf')
select * from zprt.tbl1
select * from zprt.tbl2
select * from zprt.tbl3
ALTER TABLE zprt.tbl1 add check (ID <= 1);
ALTER TABLE zprt.tbl2 add check (ID >1 AND ID <= 2);
ALTER TABLE zprt.tbl3 add check (ID >2 and ID <= 3);
ALTER TABLE zprt.tbl1 SWITCH TO zprt.final PARTITION 1
ALTER TABLE zprt.tbl2 SWITCH TO zprt.final PARTITION 2
ALTER TABLE zprt.tbl3 SWITCH TO zprt.final PARTITION 3
select * from zprt.final
Link to my blog http://notyelf.com/
July 23, 2010 at 12:31 am
I think you have misinterpreted how a sliding window actually should work.
Maintain an empty partition on both ends of the partitioned table and ensure that only empty partitions are split and merged in a sliding window scenario.
another ref: http://blogs.msdn.com/b/hanspo/archive/2009/08/21/inside-of-table-and-index-partitioning-in-microsoft-sql-server.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 23, 2010 at 3:37 pm
ALZDBA,
Thank you for the resources! However, I discovered the problem. It turns out that the column I am partitioning on can not accept a column that allows nullable values even though there were no null values in the columns. You can fix this by either adding that into the constraint itself, or change the table so the partitioned column does not allow nulls.
Link to my blog http://notyelf.com/
March 31, 2015 at 12:32 pm
This also can happen if at least on one of the tables your check constraint was created WITH NOCHECK.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply