Data not being partitioned properly?

Howdy all. Im trying to take advantage of this new feature, but it's not
working as I had expected. Below is the DDL and DML, with explanations of
what Im trying to accomplish and where my confusion is.
USE [AdventureWorks]
GO
/****** Object: PartitionFunction [myRangePF2] Script Date: 11/17/2006
15:01:28 ******/
CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
100, 1000)
/****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
15:10:35 ******/
CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
CREATE TABLE [dbo].[PartitionTest](
[PTPK] [int] IDENTITY(1,1) NOT NULL,
[salary] [int] NOT NULL,
CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
(
[PTPK] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
) ON [myRangePS2]([PTPK])
insert into PartitionTest (salary) values (1)
insert into PartitionTest (salary) values (99)
insert into PartitionTest (salary) values (999)
insert into PartitionTest (salary) values (9999)
/*
From BOL:
Partition 1 2 3 4
Values
col1 <= 1
col1 > 1 AND col1 <= 100
col1 > 100 AND col1 <= 1000
col1 > 1000
Now if I understand correctly, there should be 1 row of data in each
partition?*/
CREATE TABLE [dbo].[PartitionTestArchive](
[PTPK] [int] IDENTITY(1,1) NOT NULL,
[salary] [int] NOT NULL,
CONSTRAINT [PK_PartitionTestArchive] PRIMARY KEY CLUSTERED
(
[PTPK] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
) ON [myRangePS2]([PTPK])
/*Now I want to move all the data (value 9999) in partition 4 into my new
ParitionTestArchive table:*/
alter table PartitionTest
switch partition 4 to [PartitionTestArchive] partition 4
/*But this did nothing. So I try:*/
alter table PartitionTest
switch partition 3 to [PartitionTestArchive] partition 3
/*And that did nothing either. So I try:*/
alter table PartitionTest
switch partition 2 to [PartitionTestArchive] partition 2
/*And that moved every row of data with a value > 1 (99,999,9999) in the
table to PartitionTestArchive.*/
Again, my goal was just to move the row of data with value 9999 (partition
4) into PartitionTestArchive. So what am I not understanding? It seems that
I either don't understand the concept, or data isn't going into the
partition I think it should?
TIA, ChrisR"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:#4uzWhpCHHA.1224@.TK2MSFTNGP04.phx.gbl...
> Howdy all. Im trying to take advantage of this new feature, but it's not
> working as I had expected. Below is the DDL and DML, with explanations of
> what Im trying to accomplish and where my confusion is.
> USE [AdventureWorks]
> GO
> /****** Object: PartitionFunction [myRangePF2] Script Date: 11/17/2006
> 15:01:28 ******/
> CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
> 100, 1000)
>
> /****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
> 15:10:35 ******/
> CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
> ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
>
> CREATE TABLE [dbo].[PartitionTest](
> [PTPK] [int] IDENTITY(1,1) NOT NULL,
> [salary] [int] NOT NULL,
> CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> (
> [PTPK] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> ) ON [myRangePS2]([PTPK])
>
> insert into PartitionTest (salary) values (1)
> insert into PartitionTest (salary) values (99)
> insert into PartitionTest (salary) values (999)
> insert into PartitionTest (salary) values (9999)
>
> /*
> From BOL:
> Partition 1 2 3 4
> Values
> col1 <= 1
> col1 > 1 AND col1 <= 100
> col1 > 100 AND col1 <= 1000
> col1 > 1000
>
> Now if I understand correctly, there should be 1 row of data in each
> partition?*/
>
You partitioned the table on PTPK, not Salary.
select $PARTITION.myRangePF2([PTPK]) Partition, *
from PartitionTest
Partition PTPK salary
-- -- --
1 1 1
2 2 99
2 3 999
2 4 9999
(4 row(s) affected)
David|||Woops!
Thanks.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23PgDEopCHHA.4680@.TK2MSFTNGP04.phx.gbl...
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:#4uzWhpCHHA.1224@.TK2MSFTNGP04.phx.gbl...
> > Howdy all. Im trying to take advantage of this new feature, but it's not
> > working as I had expected. Below is the DDL and DML, with explanations
of
> > what Im trying to accomplish and where my confusion is.
> >
> > USE [AdventureWorks]
> >
> > GO
> >
> > /****** Object: PartitionFunction [myRangePF2] Script Date:
11/17/2006
> > 15:01:28 ******/
> >
> > CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
> > 100, 1000)
> >
> >
> >
> > /****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
> > 15:10:35 ******/
> >
> > CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
> > ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
> >
> >
> >
> > CREATE TABLE [dbo].[PartitionTest](
> >
> > [PTPK] [int] IDENTITY(1,1) NOT NULL,
> >
> > [salary] [int] NOT NULL,
> >
> > CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> >
> > (
> >
> > [PTPK] ASC
> >
> > )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> >
> > ) ON [myRangePS2]([PTPK])
> >
> >
> >
> > insert into PartitionTest (salary) values (1)
> >
> > insert into PartitionTest (salary) values (99)
> >
> > insert into PartitionTest (salary) values (999)
> >
> > insert into PartitionTest (salary) values (9999)
> >
> >
> >
> > /*
> >
> > From BOL:
> >
> > Partition 1 2 3 4
> >
> > Values
> >
> > col1 <= 1
> >
> > col1 > 1 AND col1 <= 100
> >
> > col1 > 100 AND col1 <= 1000
> >
> > col1 > 1000
> >
> >
> >
> > Now if I understand correctly, there should be 1 row of data in each
> > partition?*/
> >
> >
> You partitioned the table on PTPK, not Salary.
> select $PARTITION.myRangePF2([PTPK]) Partition, *
> from PartitionTest
> Partition PTPK salary
> -- -- --
> 1 1 1
> 2 2 99
> 2 3 999
> 2 4 9999
> (4 row(s) affected)
> David
>
>

Data not being partitioned properly?

Howdy all. Im trying to take advantage of this new feature, but it's not
working as I had expected. Below is the DDL and DML, with explanations of
what Im trying to accomplish and where my confusion is.
USE [AdventureWorks]
GO
/****** Object: PartitionFunction [myRangePF2] Script Date: 11/17/2006
15:01:28 ******/
CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
100, 1000)
/****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
15:10:35 ******/
CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
CREATE TABLE [dbo].[PartitionTest](
[PTPK] [int] IDENTITY(1,1) NOT NULL,
[salary] [int] NOT NULL,
CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
(
[PTPK] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
) ON [myRangePS2]([PTPK])
insert into PartitionTest (salary) values (1)
insert into PartitionTest (salary) values (99)
insert into PartitionTest (salary) values (999)
insert into PartitionTest (salary) values (9999)
/*
From BOL:
Partition 1 2 3 4
Values
col1 <= 1
col1 > 1 AND col1 <= 100
col1 > 100 AND col1 <= 1000
col1 > 1000
Now if I understand correctly, there should be 1 row of data in each
partition?*/
CREATE TABLE [dbo].[PartitionTestArchive](
[PTPK] [int] IDENTITY(1,1) NOT NULL,
[salary] [int] NOT NULL,
CONSTRAINT [PK_PartitionTestArchive] PRIMARY KEY CLUSTERED
(
[PTPK] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
) ON [myRangePS2]([PTPK])
/*Now I want to move all the data (value 9999) in partition 4 into my new
ParitionTestArchive table:*/
alter table PartitionTest
switch partition 4 to [PartitionTestArchive] partition 4
/*But this did nothing. So I try:*/
alter table PartitionTest
switch partition 3 to [PartitionTestArchive] partition 3
/*And that did nothing either. So I try:*/
alter table PartitionTest
switch partition 2 to [PartitionTestArchive] partition 2
/*And that moved every row of data with a value > 1 (99,999,9999) in the
table to PartitionTestArchive.*/
Again, my goal was just to move the row of data with value 9999 (partition
4) into PartitionTestArchive. So what am I not understanding? It seems that
I either don't understand the concept, or data isn't going into the
partition I think it should?
TIA, ChrisR
Woops!
Thanks.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23PgDEopCHHA.4680@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:#4uzWhpCHHA.1224@.TK2MSFTNGP04.phx.gbl...
of[vbcol=seagreen]
11/17/2006
> You partitioned the table on PTPK, not Salary.
> select $PARTITION.myRangePF2([PTPK]) Partition, *
> from PartitionTest
> Partition PTPK salary
> -- -- --
> 1 1 1
> 2 2 99
> 2 3 999
> 2 4 9999
> (4 row(s) affected)
> David
>
>

Data not being partitioned properly?

Howdy all. Im trying to take advantage of this new feature, but it's not
working as I had expected. Below is the DDL and DML, with explanations of
what Im trying to accomplish and where my confusion is.
USE [AdventureWorks]
GO
/****** Object: PartitionFunction [myRangePF2] Script Date: 11/17/20
06
15:01:28 ******/
CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
100, 1000)
/****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
15:10:35 ******/
CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
CREATE TABLE [dbo].[PartitionTest](
[PTPK] [int] IDENTITY(1,1) NOT NULL,
[salary] [int] NOT NULL,
CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
(
[PTPK] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
) ON [myRangePS2]([PTPK])
insert into PartitionTest (salary) values (1)
insert into PartitionTest (salary) values (99)
insert into PartitionTest (salary) values (999)
insert into PartitionTest (salary) values (9999)
/*
From BOL:
Partition 1 2 3 4
Values
col1 <= 1
col1 > 1 AND col1 <= 100
col1 > 100 AND col1 <= 1000
col1 > 1000
Now if I understand correctly, there should be 1 row of data in each
partition?*/
CREATE TABLE [dbo].[PartitionTestArchive](
[PTPK] [int] IDENTITY(1,1) NOT NULL,
[salary] [int] NOT NULL,
CONSTRAINT [PK_PartitionTestArchive] PRIMARY KEY CLUSTERED
(
[PTPK] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
) ON [myRangePS2]([PTPK])
/*Now I want to move all the data (value 9999) in partition 4 into my new
ParitionTestArchive table:*/
alter table PartitionTest
switch partition 4 to [PartitionTestArchive] partition 4
/*But this did nothing. So I try:*/
alter table PartitionTest
switch partition 3 to [PartitionTestArchive] partition 3
/*And that did nothing either. So I try:*/
alter table PartitionTest
switch partition 2 to [PartitionTestArchive] partition 2
/*And that moved every row of data with a value > 1 (99,999,9999) in the
table to PartitionTestArchive.*/
Again, my goal was just to move the row of data with value 9999 (partition
4) into PartitionTestArchive. So what am I not understanding? It seems that
I either don't understand the concept, or data isn't going into the
partition I think it should?
TIA, ChrisR
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:#4uzWhpCHHA.1224@.TK2MSFTNGP04.phx.gbl...
> Howdy all. Im trying to take advantage of this new feature, but it's not
> working as I had expected. Below is the DDL and DML, with explanations of
> what Im trying to accomplish and where my confusion is.
> USE [AdventureWorks]
> GO
> /****** Object: PartitionFunction [myRangePF2] Script Date: 11/17/
2006
> 15:01:28 ******/
> CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (
1,
> 100, 1000)
>
> /****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/20
06
> 15:10:35 ******/
> CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
> ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
>
> CREATE TABLE [dbo].[PartitionTest](
> [PTPK] [int] IDENTITY(1,1) NOT NULL,
> [salary] [int] NOT NULL,
> CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
> (
> [PTPK] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [myRangePS2]([PTPK])
> ) ON [myRangePS2]([PTPK])
>
> insert into PartitionTest (salary) values (1)
> insert into PartitionTest (salary) values (99)
> insert into PartitionTest (salary) values (999)
> insert into PartitionTest (salary) values (9999)
>
> /*
> From BOL:
> Partition 1 2 3 4
> Values
> col1 <= 1
> col1 > 1 AND col1 <= 100
> col1 > 100 AND col1 <= 1000
> col1 > 1000
>
> Now if I understand correctly, there should be 1 row of data in each
> partition?*/
>
You partitioned the table on PTPK, not Salary.
select $PARTITION.myRangePF2([PTPK]) Partition, *
from PartitionTest
Partition PTPK salary
-- -- --
1 1 1
2 2 99
2 3 999
2 4 9999
(4 row(s) affected)
David|||Woops!
Thanks.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23PgDEopCHHA.4680@.TK2MSFTNGP04.phx.gbl...
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:#4uzWhpCHHA.1224@.TK2MSFTNGP04.phx.gbl...
of[vbcol=seagreen]
11/17/2006[vbcol=seagreen]
> You partitioned the table on PTPK, not Salary.
> select $PARTITION.myRangePF2([PTPK]) Partition, *
> from PartitionTest
> Partition PTPK salary
> -- -- --
> 1 1 1
> 2 2 99
> 2 3 999
> 2 4 9999
> (4 row(s) affected)
> David
>
>