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.

19 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.

ravinder 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...

Carrie, suggesting that all thrift shop. ugg boots Began by simply Coco Chanel within the 1900 it now grow to be an icon involving haute couture and every thing high-class. ugg boots on sale It is not popular like green or blue but can help you in creating stunning looks. http://www.downuggboots.com Zappos has over 20% market share in the online footwear market. ugg canada Like her son George, Little road did not resemble the Miss on buy cheap chanel bags and shoes in new orleans, louisiana usa point she bound by that until countermanded Andrew in clothing.

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

Paul Amarante said...

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

oakleyses said...

louis vuitton outlet online, kate spade handbags, ray ban sunglasses, kate spade outlet online, prada outlet, michael kors outlet, coach outlet store online, nike shoes, nike air max, jordan shoes, nike free, polo ralph lauren, christian louboutin shoes, longchamp outlet, michael kors outlet online, longchamp handbags, gucci handbags, longchamp outlet online, polo ralph lauren outlet, michael kors outlet store, michael kors outlet online, oakley vault, red bottom shoes, prada handbags, tory burch outlet, cheap oakley sunglasses, michael kors outlet online, burberry outlet online, chanel handbags, oakley sunglasses, burberry outlet online, coach purses, louis vuitton outlet, christian louboutin outlet, tiffany jewelry, coach outlet, nike air max, tiffany and co jewelry, ray ban outlet, louboutin shoes, louis vuitton handbags, michael kors outlet online, louis vuitton, coach outlet, louis vuitton outlet

oakleyses said...

north face, sac michael kors, abercrombie and fitch, scarpe hogan, nike air max, hollister, louis vuitton, nike free, true religion outlet, ray ban uk, vans pas cher, oakley pas cher, ray ban pas cher, lululemon, hollister, air max pas cher, michael kors canada, north face pas cher, air jordan, longchamp pas cher, nike trainers, nike free pas cher, hermes pas cher, air max, sac louis vuitton, true religion outlet, guess pas cher, converse pas cher, true religion, mulberry, nike huarache, timberland, ralph lauren pas cher, burberry pas cher, nike air force, sac vanessa bruno, lacoste pas cher, louis vuitton uk, nike roshe run, chaussure louboutin, longchamp, ralph lauren, barbour, nike roshe, nike blazer pas cher, michael kors uk, tn pas cher, new balance pas cher, louis vuitton pas cher, nike air max

oakleyses said...

giuseppe zanotti, insanity workout, canada goose, valentino shoes, bottega veneta, new balance outlet, canada goose pas cher, marc jacobs outlet, moncler, asics shoes, beats headphones, hollister, reebok shoes, ugg boots, canada goose outlet, vans outlet, rolex watches, ugg outlet, moncler outlet, canada goose outlet, mac cosmetics, lululemon outlet, uggs outlet, wedding dresses, ferragamo shoes, soccer shoes, babyliss pro, north face outlet, mont blanc pens, jimmy choo shoes, mcm handbags, longchamp, ugg, canada goose uk, soccer jerseys, ghd, birkin bag, uggs on sale, celine handbags, moncler, nfl jerseys, roshe run, chi flat iron, instyler ionic styler, herve leger, p90x workout, ugg soldes, abercrombie and fitch, north face jackets, canada goose outlet

ninest123 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

ninest123 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

ninest123 said...

ralph lauren, babyliss, bottega veneta, ipad cases, louboutin, baseball bats, ferragamo shoes, iphone 6 plus cases, mac cosmetics, iphone 6s plus cases, longchamp, lululemon, herve leger, chi flat iron, nfl jerseys, iphone cases, valentino shoes, hollister, new balance, nike trainers, hollister, beats by dre, iphone 5s cases, iphone 6 cases, celine handbags, north face outlet, oakley, mcm handbags, abercrombie and fitch, wedding dresses, soccer jerseys, insanity workout, soccer shoes, nike air max, vans shoes, instyler, nike huarache, nike roshe, north face outlet, p90x workout, ghd, hollister, iphone 6s cases, nike air max, birkin bag, timberland boots, jimmy choo shoes, s5 cases, reebok shoes, giuseppe zanotti, mont blanc, asics running shoes

ninest123 said...

ugg,uggs,uggs canada, canada goose, ugg,ugg australia,ugg italia, canada goose, hollister, wedding dresses, moncler, louis vuitton, canada goose outlet, swarovski, pandora charms, pandora jewelry, moncler outlet, moncler, sac louis vuitton pas cher, replica watches, louis vuitton, marc jacobs, louis vuitton, vans, moncler, swarovski crystal, pandora jewelry, toms shoes, moncler, juicy couture outlet, doudoune canada goose, karen millen, converse, lancel, canada goose outlet, coach outlet, gucci, ray ban, thomas sabo, moncler, supra shoes, canada goose uk, juicy couture outlet, links of london, barbour jackets, louis vuitton, canada goose, barbour, ugg boots uk, pandora charms, bottes ugg, converse outlet, moncler, montre pas cher, canada goose, ugg pas cher, moncler
ninest123 16.03