Repost: Data not being partitioned properly?

Thanks to David Browne for pointing out my mistake yesterday. But I have
implemented the suggestion and am still experiencing the same behavior. I'm
sorry to to keep posting partioning questions here, but the concept has
sparked a huge interest at my work and I need to answer lots of questions.
Therefore, Im doing lots of different tests/ secenarios and it seems like
each answer brings up more questions. Anyways, 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, 10000)
/****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
15:10:35 ******/
CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
([PRIMARY], [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]([salary])
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]([salary])
/*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
> 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]([salary])
The issue here is that "ON [myRangePS2]([salary])" is not used because of
the clustered primary key "ON [myRangePS2]([PTPK])" specification. The
clustered index determines the partitioning of data so all data is
partitioned on PTPK instead of salary as you intended.
If your objective is to use SWITCH as a means to quickly archive data,
you'll want to align the table and index partitions on a date value. See
aligned indexes in the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:OBsDVezCHHA.4016@.TK2MSFTNGP02.phx.gbl...
> Thanks to David Browne for pointing out my mistake yesterday. But I have
> implemented the suggestion and am still experiencing the same behavior.
> I'm
> sorry to to keep posting partioning questions here, but the concept has
> sparked a huge interest at my work and I need to answer lots of questions.
> Therefore, Im doing lots of different tests/ secenarios and it seems like
> each answer brings up more questions. Anyways, 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, 10000)
>
> /****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
> 15:10:35 ******/
> CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
> ([PRIMARY], [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]([salary])
>
> 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]([salary])
>
> /*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
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|||> 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]([salary])
The issue here is that "ON [myRangePS2]([salary])" is not used because of
the clustered primary key "ON [myRangePS2]([PTPK])" specification. The
clustered index determines the partitioning of data so all data is
partitioned on PTPK instead of salary as you intended.
If your objective is to use SWITCH as a means to quickly archive data,
you'll want to align the table and index partitions on a date value. See
aligned indexes in the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:OBsDVezCHHA.4016@.TK2MSFTNGP02.phx.gbl...
> Thanks to David Browne for pointing out my mistake yesterday. But I have
> implemented the suggestion and am still experiencing the same behavior.
> I'm
> sorry to to keep posting partioning questions here, but the concept has
> sparked a huge interest at my work and I need to answer lots of questions.
> Therefore, Im doing lots of different tests/ secenarios and it seems like
> each answer brings up more questions. Anyways, 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, 10000)
>
> /****** Object: PartitionScheme [myRangePS2] Script Date: 11/17/2006
> 15:10:35 ******/
> CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
> ([PRIMARY], [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]([salary])
>
> 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]([salary])
>
> /*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
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|||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...[vbcol=seagreen]
> The issue here is that "ON [myRangePS2]([salary])" is not used because of
> the clustered primary key "ON [myRangePS2]([PTPK])" specification. The
> clustered index determines the partitioning of data so all data is
> partitioned on PTPK instead of salary as you intended.
> If your objective is to use SWITCH as a means to quickly archive data,
> you'll want to align the table and index partitions on a date value. See
> aligned indexes in the Books Online for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:OBsDVezCHHA.4016@.TK2MSFTNGP02.phx.gbl...
questions.[vbcol=seagreen]
like[vbcol=seagreen]
11/17/2006[vbcol=seagreen]
new[vbcol=seagreen]
(partition
>
|||"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[vbcol=seagreen]
> 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
> (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 KEY
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] PRIMARY
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
John