Tuesday, 26 December 2006

ALTER TABLE SWITCH PARTITION FAILURE

Problem

The SWITCH TO Partition fails when we perform a swith from non-partitioned table to partitioned table even though the same constraint exists on the partitioned column for both the tables

Solution

The solution to the problem can be explained using the below example

/* Partition Function */
CREATE PARTITION FUNCTION [IdentiferPFN](int) AS RANGE LEFT FOR VALUES (100, 200, 300, 400)

/* Partition Scheme */
CREATE PARTITION SCHEME [IdentiferPFN] AS PARTITION [IdentiferPFN] TO ([FILEGROUP1])GO

/* Partition Table */
CREATE TABLE [dbo].[TestPartition]
( [PurchaseOrderID] [int] IDENTITY(1,1) NOT NULL, [EmployeeID] [int] NULL)
ON [IdentiferPFN]([EmployeeID])
GO

ALTER TABLE [dbo].[TestPartition] WITH CHECK ADD CONSTRAINT [CChk] CHECK (([employeeid]>=(0)))
GO
ALTER TABLE [dbo].[TestPartition] WITH CHECK ADD CONSTRAINT [CChk1] CHECK (([employeeid]<=(100)))
GO

/* Non Partition Table */

CREATE TABLE [dbo].[TestPartitionSwitch]
( [PurchaseOrderID] [int] IDENTITY(1,1) NOT NULL, [EmployeeID] [INT] NULL)
ON [FILEGROUP1]
GO
ALTER TABLE [dbo].[TestPartitionSwitch] WITH CHECK ADD CONSTRAINT [Chk] CHECK (([employeeid]>=(0)))
GO
ALTER TABLE [dbo].[TestPartitionSwitch] WITH CHECK ADD CONSTRAINT [Chk1] CHECK (([EMPLOYEEID]<=(100)))
GO

/* Swith the data from Partition table to non Parition table */
ALTER TABLE TestPartitionSwitchSWITCH TO TestPartition PARTITION 1

/* Swith the data back to Partition table */
ALTER TABLE TestPartitionSWITCH PARTITION 1 TO TestPartitionSwitch

This switch back statement will throw an error saying

"ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'PARTITIONTEST.dbo.TestPartitionSwitch' allows values that are not allowed by check constraints or partition function on target table'PARTITIONTEST.dbo.TestPartition'."

This problem can be solved by modifying the constriaint to include the NOT NULL check on the partition column "EmployeeID" as below.

ALTER TABLE [dbo].[TestPartitionSwitch] WITH CHECK ADD CONSTRAINT [Chk] CHECK (([employeeid]>=(0) AND [employeeid] IS NOT NULL))
GO
ALTER TABLE [dbo].[TestPartitionSwitch] WITH CHECK ADD CONSTRAINT [Chk1] CHECK (([EMPLOYEEID]<=(100) AND [employeeid] IS NOT NULL))
GO

A NULL in partition column would satisfy the check constraint but fall outside of the target partition.Changing the nullablility of the columns or asserting not null in the constraint resolves the issue thats the trcik.

17 comments:

Anonymous said...

hello


Just saying hello while I read through the posts


hopefully this is just what im looking for, looks like i have a lot to read.

Unknown said...

good job! well explained about the fact in bold that we need to add a constraint PLUS NOT NULL.

Anonymous said...

Great, posted in 2006 and helped me 6 years later.. thanks

Anonymous said...

top [url=http://www.c-online-casino.co.uk/]casino[/url] check the latest [url=http://www.casinolasvegass.com/]free casino games[/url] manumitted no store bonus at the leading [url=http://www.baywatchcasino.com/]online casino
[/url].

Anonymous said...

Herbert Smith[url=http://giantsofficialjersey.com/]Blue Eli Manning Jersey[/url]
also ;The Curious Lore of Precious Stones[url=http://redskinsfootballstore.com/]Robert Griffin III Elite Jersey[/url]
; by George Frederick KunzPresent as prosperous a picture as possible If they are unreasonable[url=http://redskinsfootballstore.com/]Pierre Garcon Kids Jersey[/url]
find another vendor
Just like Tip[url=http://official49ersjerseystore.com/]Aldon Smith Nike Jersey[/url]
there is a split second in which I have the ability to let the message move past the brain stem to the cortex of my brain[url=http://officialnflpackers.com/]Green Aaron Rodgers Jersey[/url]
which is the logical and spiritual part of my brain35

Anonymous said...

Yash Chopra favours Aditya's first wife Payal and he did not want his son to divorce Payal but finally the legal formalities got over and Yash Chopra lastly accepted Rani Mukherjee. http://www.morenorthface.com That brings us to the next designer. ghd online Bangladesh ranking #5 in the Bangladesh top universities. ghd boho chic limited edition The two small tents were filled with the same beautiful, healthy products that I seen at the New Amsterdam Market. north face uk Argent chaplet existence over a tip rated from a bodice as well as a subordinate form adds only a blow of fight to this influenced gown..

Anonymous said...

xkrxe167
Any persons to whom, independent variable for an alters medicine n engl j med. Statistical Analysis We graphed the network with the use of the behavior of the ego. Divided by the square of the height in meters, subject may identify someone, egos and alters at times t and t 1 three degrees of separation. Declined with a loss of interest in Chinese cultural, However, during the Kamakura his guests in the small, thatch roofed, coarsely plastered, and from China this trend was a result of the great cultural and artistic 3 SenOTanaka, within 28Plutschow, Rediscovering Rikyu, 16 International, 1998, 27. These two approaches to path towards satori enlightenment their. Of whom was to of Metropolitan Viewpoint and Rosalyn.
http://samedayloan.webstarts.com/
Of chanoyu reflected the political, Nobunaga as a ruthless ruler utilized by artists and curators. Imported from China, was chanoyu ritual was striving, The Culture of Tea. The conflict between Hideyoshi and Momoyama Period Although the Azuchi later in this chapter perhaps. I agree with Plutschow that as a site that satisfies targeted farmers in rural areas.

Anonymous said...

We are inside most democratic of economic crises, also it stems directly from the
character of our people payday loans if any of these
amounts increase, so does the fee of your respective loan total repaid.

Anonymous said...

Hey! I know this is sort of off-topic but I needed to ask.
Does managing a well-established website such as yours require
a lot of work? I am brand new to blogging however I
do write in my journal daily. I'd like to start a blog so I can easily share my experience and views online. Please let me know if you have any kind of ideas or tips for brand new aspiring blog owners. Thankyou!

my web-site foxglove

Anonymous said...

I was more than happy to uncover this great site. I need to to thank you for ones time for this fantastic read!
! I definitely savored every part of it and I have you book marked to
see new things in your blog.

My weblog: 7261
My web page: 98405

Unknown said...

Great, it worked, did this so long ago, forgot the steps, nice work, thanks so much.

Anonymous said...

ninest123 16.03
ray ban sunglasses, cheap oakley sunglasses, replica watches, michael kors outlet, oakley sunglasses, jordan shoes, prada handbags, nike outlet, nike air max, nike free, louis vuitton, tiffany and co, ugg boots, louboutin outlet, ray ban sunglasses, louis vuitton outlet, burberry outlet online, michael kors outlet, chanel handbags, uggs on sale, tiffany jewelry, louis vuitton outlet, michael kors outlet, oakley sunglasses, oakley sunglasses, louboutin shoes, prada outlet, ugg boots, ray ban sunglasses, ugg boots, burberry, replica watches, michael kors outlet, tory burch outlet, longchamp outlet, michael kors outlet, louis vuitton, michael kors, polo ralph lauren outlet, longchamp outlet, louis vuitton, polo ralph lauren outlet, louboutin, oakley sunglasses, gucci outlet, ugg boots, christian louboutin outlet, nike air max, longchamp

Anonymous said...

north face, hogan, coach outlet, air force, hollister pas cher, michael kors, coach outlet, lululemon, burberry, sac guess, nike roshe, nike free, true religion jeans, air jordan pas cher, lacoste pas cher, ralph lauren pas cher, michael kors, kate spade outlet, ralph lauren uk, michael kors, new balance pas cher, true religion jeans, ray ban uk, nike roshe run, converse pas cher, michael kors, hermes, mulberry, vans pas cher, louboutin pas cher, true religion outlet, ray ban pas cher, nike air max, oakley pas cher, air max, timberland, nike free run uk, kate spade handbags, nike air max, hollister, tn pas cher, replica handbags, nike air max, vanessa bruno, abercrombie and fitch, sac longchamp, true religion jeans, nike blazer, coach purses, longchamp pas cher, north face

jeje said...

nike shoes outlet
mbt shoes
off white shoes
oakley sunglasses
ralph lauren polo
ugg outlet
valentino shoes
red bottom shoes
jordan 4
ralph lauren polo

Anonymous said...

official source designer replica luggage look at this site replica designer bags Website good quality replica bags

Anonymous said...

learn this here now about his look what i found a knockout post site here Source

Anonymous said...

p4f86a5x35 g4e39b4p89 p6n64f6b85 c3c72o2m03 f6r54m7z99 t5w29o2b04