Please disregard my last post, I'm going to start another thread.

"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:O8aM%23m2CHHA.1224@.TK2MSFTNGP04.phx.gbl...
> For clarification, are you saying that the placement of the Primary Key
> "overrides" where I had placed the partitioning?
> Also, if that's the case, and I want to quickly archive data as you
> mentioned, then wouldn't I need to have my PK's on the date column
(provided
> thats the column I wanted to SWITCH, which of course it most likely would
> be)?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:7B86860D-747C-4D63-99BE-18C6EA3CF498@.microsoft.com...
of[vbcol=seagreen]
See[vbcol=seagreen]
have[vbcol=seagreen]
behavior.[vbcol=seagreen]
has[vbcol=seagreen]
> questions.
> like
DML,[vbcol=seagreen]
confusion[vbcol=seagreen]
> 11/17/2006
(1,[vbcol=seagreen]
11/17/2006[vbcol=seagreen]
> new
the[vbcol=seagreen]
> (partition
>Hi Chris
Inline is your script corrected to show it working. You may want to look at
and try the SQL Server samples for partitioning and sliding window
http://msdn2.microsoft.com/en-us/library/ms160726.aspx
CREATE DATABASE TESTPARTITION
GO
USE TESTPARTITION
GO
CREATE PARTITION FUNCTION [myRangePF2](int) AS RANGE LEFT FOR VALUES (1,
100, 1000, 10000)
GO
CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
GO
CREATE TABLE [dbo].[PartitionTest](
[PTPK] [int] IDENTITY(1,1) NOT NULL ,
[salary] [int] NOT NULL CONSTRAINT [PK_PartitionTest] PRIMARY KE
Y
CLUSTERED
) ON [myRangePS2]([salary])
GO
insert into PartitionTest (salary) values (1)
insert into PartitionTest (salary) values (99)
insert into PartitionTest (salary) values (999)
insert into PartitionTest (salary) values (9999)
GO
/* Show partitions */
SELECT 1 AS Value, $PARTITION.myRangePF2(1) As Partition
UNION ALL SELECT 99, $PARTITION.myRangePF2(99)
UNION ALL SELECT 999, $PARTITION.myRangePF2(999)
UNION ALL SELECT 9999, $PARTITION.myRangePF2(9999)
GO
CREATE TABLE [dbo].[PartitionTestArchive](
[PTPK] [int] IDENTITY(1,1) NOT NULL,
[salary] [int] NOT NULL CONSTRAINT [PK_PartitionTestArchive] PRI
MARY
KEY CLUSTERED
) ON [myRangePS2]([salary])
GO
SELECT * FROM [dbo].[PartitionTest]
SELECT * FROM [dbo].[PartitionTestArchive]
GO
/*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
GO
SELECT * FROM [dbo].[PartitionTest]
SELECT * FROM [dbo].[PartitionTestArchive]
GO
alter table PartitionTest
switch partition 3 to [PartitionTestArchive] partition 3
GO
SELECT * FROM [dbo].[PartitionTest]
SELECT * FROM [dbo].[PartitionTestArchive]
GO
alter table PartitionTest
switch partition 2 to [PartitionTestArchive] partition 2
GO
SELECT * FROM [dbo].[PartitionTest]
SELECT * FROM [dbo].[PartitionTestArchive]
GO
alter table PartitionTest
switch partition 1 to [PartitionTestArchive] partition 1
GO
SELECT * FROM [dbo].[PartitionTest]
SELECT * FROM [dbo].[PartitionTestArchive]
GO
Johnsql