August 29, 2016 at 6:36 am
Hi guys,
I have a partition function as
Create partition function partfunct_test(date time) as range left for value('20141231 23.59.59.987',(20151231 23.59.59.987')
Create partition scheme partsch_test as partition partfunct all to (primary)
Create table partitiontable
(
Col1 int, col2 int, col3 varchar(255), col4 varchar(255), businessdate datetime
) on partsch_test(businessdate)
Also I have non partitioned table
Create table nonpartitiontable2014
(
Col1 int, col2 int, col3 varchar(255), col4 varchar(255), businessdate datetime
)
And same non partitioned table for year 2015 and 2016.
After populating data in these non partitioned tables, I created check constraints like these:
Alter table nonpartitiontable2014 with check add constraint checkconstraint2014 check (businessdate is not null and businessdate < '2015-01-01')
Now finally when I tried performing switching partition, it gave error
Alter table nonpartitiontable2014 switch to partitiontable partition 1
Error it gives is
"Alter table switch partition failed. Check constraints or partition function of source table nonpartitiontable2014 allows values that are not allowed by check constraints or partition function on target table partitiontable" Error no is 4972.
August 29, 2016 at 6:41 am
These are not the same
range left for value('20141231 23.59.59.987',(20151231 23.59.59.987')
check (businessdate is not null and businessdate < '2015-01-01')
Specifically, the value '2014-12-21 23:59:59.990' satisfies the constraint but does not belong in the partition you're trying to switch the table into.
Either you need to change the constraint to be (businessdate <= '20141231 23.59.59.987) or, and I recommend this, change the partition function to be (range right for value ('2015-01-01', '2016-01-01'), then you don't have to worry about rounding of datetime values
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 29, 2016 at 7:10 am
Perfect. Changing partition function to just date worked perfect. Thanks a lot Gail.
August 30, 2016 at 12:00 am
Hi Gail,
One more question. Can I have more no of partitions then the no of filegroups ? Though my partition scheme says all partition should be in primary filegroup.
August 30, 2016 at 4:52 am
Number of filegroups and number of partitions are entirely separate things. If your partition scheme says all to primary, then all partitions are going into the primary filegroup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2016 at 1:43 am
GilaMonster (8/30/2016)
Number of filegroups and number of partitions are entirely separate things. If your partition scheme says all to primary, then all partitions are going into the primary filegroup.
Hi Gail, facing problem again. Currently I have to save data for 5 years so creating four partitions with date values and my scheme says all pointing to Primary filegroup. Now the number of years can grow and eventually current year will also move to 2017. So I'll need new partitions. For that I am creating new procedure which will check if it's new year. If yes will create a partition for say 2017 with alter partition split and merge the last one. If the number of years to save data changes from 5 to say 8 then it will create three DMLs with alter partition split for new boundaries for old years. Now problem is though my scheme says all partition to primary, it gives error saying "associated partition function 'myfunction' generates more partitions than there are filegroups mentioned in the scheme 'myscheme'". It's Msg 7707, level 16, state 1
September 12, 2016 at 2:35 am
I got it. I have to run the alter scheme after every alter partition to use primary filegroup.
September 12, 2016 at 3:28 am
Yup, you have to set the next filegroup to be used after each split, even if all the partitions are going to the same filegroup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply