creating new partition files on table

Question

creating new partition files on table

Viewing 8 posts - 31 through 37 (of 37 total)

You must be logged in to reply to this topic. Login to reply

lkennedy76 SSChampion Points: 13683 More actions · Answer 1

They won't switch back into the partitioned table, I haven't moved anything yet, I was just testing and preparing to make sure I had everything correct. I thought I will switch it out and back in, nope. I scripted them out perfect, they are identical. I have altered the Inventory201604 table several times. The only columns that are not null are in the clustered index that I need, if I remove it, it says that I need the clustered index for the switch to work and I end up back here with this error message.

I even found the requirements for switching and I followed them to the letter.

https://technet.microsoft.com/en-us/library/ms191160(v=sql.105).aspx

ALTER TABLE SWITCH statement failed. Check constraints of source table 'Inventory201604' allow values that are not allowed by range defined by partition 14 on target table 'Inventory'.

MCSE SQL Server 2012\2014\2016

RAThor SSC Eights! Points: 941 More actions · Answer 2

lkennedy76 - Saturday, April 8, 2017 5:41 PM
hello all,
I have a table that has 2,948,231,398 billion rows...I know..

You have a table w/ almost 3 quintillion rows??

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

No. It has 2.9 billion rows (see the output of sys.dm_db_partiton_stats earlier)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

lkennedy76 - Wednesday, April 26, 2017 9:25 AM
They won't switch back into the partitioned table,
ALTER TABLE SWITCH statement failed. Check constraints of source table 'Inventory201604' allow values that are not allowed by range defined by partition 14 on target table 'Inventory'.

The error message says exactly why the switch fails. The check constraints don't match the range allowed by the partition boundaries. Check your constraints, make sure they're correct (with the correct inequalities) for the range of rows allowed by the partition that the table will be switched into.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

lkennedy76 - Tuesday, April 25, 2017 8:28 AM
Will I switch the inventory_deafault file group back to the inventory table?

Huh? You don't switch filegroups into tables.

And the very first step I listed was switching out the partition with 2.4 billion rows into its own table.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

lkennedy76 SSChampion Points: 13683 More actions · Answer 6

GilaMonster - Thursday, April 27, 2017 8:16 AM
lkennedy76 - Tuesday, April 25, 2017 8:28 AM
Will I switch the inventory_deafault file group back to the inventory table?
Huh? You don't switch filegroups into tables.
And the very first step I listed was switching out the partition with 2.4 billion rows into its own table.

MCSE SQL Server 2012\2014\2016

lkennedy76 SSChampion Points: 13683 More actions · Answer 7

GilaMonster - Thursday, April 27, 2017 8:14 AM
lkennedy76 - Wednesday, April 26, 2017 9:25 AM
They won't switch back into the partitioned table,
ALTER TABLE SWITCH statement failed. Check constraints of source table 'Inventory201604' allow values that are not allowed by range defined by partition 14 on target table 'Inventory'.
The error message says exactly why the switch fails. The check constraints don't match the range allowed by the partition boundaries. Check your constraints, make sure they're correct (with the correct inequalities) for the range of rows allowed by the partition that the table will be switched into.

I checked all the constraints and they match. Do you have a query that I can compare, I may be missing something or not seeing it.

MCSE SQL Server 2012\2014\2016

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

lkennedy76 - Thursday, April 27, 2017 8:38 AM
GilaMonster - Thursday, April 27, 2017 8:14 AM
lkennedy76 - Wednesday, April 26, 2017 9:25 AM
They won't switch back into the partitioned table,
ALTER TABLE SWITCH statement failed. Check constraints of source table 'Inventory201604' allow values that are not allowed by range defined by partition 14 on target table 'Inventory'.
The error message says exactly why the switch fails. The check constraints don't match the range allowed by the partition boundaries. Check your constraints, make sure they're correct (with the correct inequalities) for the range of rows allowed by the partition that the table will be switched into.
I checked all the constraints and they match. Do you have a query that I can compare, I may be missing something or not seeing it.

If you're getting that error, the constraints don't match the partition boundaries. There's no other reason you'd get that precise error.
Check that you've got the > vs >= and < vs <= correct, it's very easy to get those wrong, and make sure that you have the datetimes specified down to the millisecond.
RANGE LEFT, which is what you have, means that the boundary value is found in the partition to the left of the boundary (I normally prefer and recommend RANGE RIGHT for datetimes), which means your constraint should be > lower bound AND <= upper bound

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass