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