Partition switching

6 Apr 2006 11:52 AM

vsr

Hello,

I've encountered with a problem trying to switch the unpartitioned
table to the partition of partitioned table. Here is an example.

Let's create partition function and partition scheme in some database:

CREATE PARTITION FUNCTION [pf1](int)
AS RANGE LEFT
FOR VALUES (100, 200)
GO

CREATE PARTITION SCHEME [ps1]
AS PARTITION [pf1]
TO ([PRIMARY], [PRIMARY], [PRIMARY])
GO

Now let's create a partitioned table:

create table pt (c1 int) on [ps1]([c1])
go

and an unpartitioned one:

create table unpt (c1 int) on [PRIMARY]
go

So, I have the partitioned table [pt] which consists of three
partitiones: <=100, 101..200 and >200. All of partitions are

located in filegroup PRIMARY. Also I have unpartitioned table [unpt]
which is also located in filegroup PRIMARY. Both tables

are empty and don't have any primary or foreign keys, indexes,
constraints and so on - NOTHING.

Now let's try to switch [unpt] to the 2nd partition of the table [pt]:

alter table [unpt] switch to [pt] partition 2
go

Trying to execute this I get the following error:

"ALTER TABLE SWITCH statement failed. Check constraints or partition
function of source table 'Partitioning.dbo.unpt' allows

values that are not allowed by check constraints or partition function
on target table 'Partitioning.dbo.pt'".

Why? There are no constraints anywhere. Ok, maybe SQL Server needs to
ensure that the data of [unpt] meet the requirements of

partition function [pf1] to put them into partition 2? Let's add such a
constraint:

alter table unpt add constraint c1_constr check (c1>=110 and c1<=150)
go

The range 110..150 is a subrange of 100..200. Why do I get the same
error in this case? How to make such a scenario work?

Thank you in advance.

6 Apr 2006 3:39 PM

David Browne

Show quote

"vsr" <sergei.vakhi***@quest.com> wrote in message
news:1144324374.843535.186920@t31g2000cwb.googlegroups.com...
> Hello,
>
> I've encountered with a problem trying to switch the unpartitioned
> table to the partition of partitioned table. Here is an example.
>
> Let's create partition function and partition scheme in some database:
>
> CREATE PARTITION FUNCTION [pf1](int)
> AS RANGE LEFT
> FOR VALUES (100, 200)
> GO
>
> CREATE PARTITION SCHEME [ps1]
> AS PARTITION [pf1]
> TO ([PRIMARY], [PRIMARY], [PRIMARY])
> GO
>
> Now let's create a partitioned table:
>
> create table pt (c1 int) on [ps1]([c1])
> go
>
> and an unpartitioned one:
>
> create table unpt (c1 int) on [PRIMARY]
> go
>
> So, I have the partitioned table [pt] which consists of three
> partitiones: <=100, 101..200 and >200. All of partitions are
>
> located in filegroup PRIMARY. Also I have unpartitioned table [unpt]
> which is also located in filegroup PRIMARY. Both tables
>
> are empty and don't have any primary or foreign keys, indexes,
> constraints and so on - NOTHING.
>
> Now let's try to switch [unpt] to the 2nd partition of the table [pt]:
>
> alter table [unpt] switch to [pt] partition 2
> go
>
> Trying to execute this I get the following error:
>
> "ALTER TABLE SWITCH statement failed. Check constraints or partition
> function of source table 'Partitioning.dbo.unpt' allows
>
> values that are not allowed by check constraints or partition function
> on target table 'Partitioning.dbo.pt'".
>
> Why? There are no constraints anywhere. Ok, maybe SQL Server needs to
> ensure that the data of [unpt] meet the requirements of
>
> partition function [pf1] to put them into partition 2? Let's add such a
> constraint:
>
> alter table unpt add constraint c1_constr check (c1>=110 and c1<=150)
> go
>
> The range 110..150 is a subrange of 100..200. Why do I get the same
> error in this case? How to make such a scenario work?
>

The check constraint must match the partition range exactly. Here that
would be:

alter table unpt add constraint c1_constr check (c1>=101 and c1<=200)

Another good option is to use a second partitioned table for your switching.
You can then switch a partition from one table to the other:

create table stage (c1 int) on [ps1]([c1])
alter table [stage] switch partition 2 to [pt] partition 2

David

7 Apr 2006 7:27 AM

vsr

I tried this:

alter table unpt add constraint c1_constr check (c1>=101 and c1<=200)

and got exactly the same error... So, it doesn't work.

But your second option really helps, thanks.