Showing posts with label partitioned. Show all posts
Showing posts with label partitioned. Show all posts

Sunday, March 25, 2012

Data Sliles or Filter or seperate Fact Tables for ourCube Partitio

Hi,
we have cubes partitioned on the time dimension. The majority of data that
comes in if for the last 4 days or so. The new data is loaded into a seperate
fact table, and loaded into a partition. This partition is then merged into
the current month partition.
However, we can have data that comes in which is a month or longer old. So
would be merged into the incorrect partition. And would remain there until a
full reprocess of the Fact tables are cube partitiions.
I understand that when data slices are defined for a partition, that AS2000
will only visit the partitions that contain the relevent data. What happens
if we use data slices in our situation?
If the data is in the incorrect partition, as a result of merging, is it
possible that the data is missed out, depending on the query? Or would the
merging not be allowed to take place?
Would we want to define data slices for all partitions, except for the
current month ?
Or would we want to define a filter, and rather than a build and merge, do
an incremental build against the cube ?
We are really looking for the quickest load time possible, while having
correct data of course.
Thanks in advance for any advice and help.
Is this for AS 2000 or 2005? In 2000, slices that you defined are used for
querying. In 2005, slices for MOLAP partitions are detected automatically.
For ROLAP partitions the slice you specify will be used to eliminate
partitions from queries.
But besides that, only your latest partition would have "incorrect" data,
right? So just don't set a slice for that last partition -- it would mean
that this partition would be unnecessarily scanned for some queries, but it
should be a fairly small partition anyway...
Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:370E22EC-6304-49F2-B01D-812B37B2E1EF@.microsoft.com...
> Hi,
> we have cubes partitioned on the time dimension. The majority of data that
> comes in if for the last 4 days or so. The new data is loaded into a
> seperate
> fact table, and loaded into a partition. This partition is then merged
> into
> the current month partition.
> However, we can have data that comes in which is a month or longer old. So
> would be merged into the incorrect partition. And would remain there until
> a
> full reprocess of the Fact tables are cube partitiions.
> I understand that when data slices are defined for a partition, that
> AS2000
> will only visit the partitions that contain the relevent data. What
> happens
> if we use data slices in our situation?
> If the data is in the incorrect partition, as a result of merging, is it
> possible that the data is missed out, depending on the query? Or would the
> merging not be allowed to take place?
> Would we want to define data slices for all partitions, except for the
> current month ?
> Or would we want to define a filter, and rather than a build and merge, do
> an incremental build against the cube ?
> We are really looking for the quickest load time possible, while having
> correct data of course.
> Thanks in advance for any advice and help.
>

Data Sliles or Filter or seperate Fact Tables for ourCube Partitio

Hi,
we have cubes partitioned on the time dimension. The majority of data that
comes in if for the last 4 days or so. The new data is loaded into a seperat
e
fact table, and loaded into a partition. This partition is then merged into
the current month partition.
However, we can have data that comes in which is a month or longer old. So
would be merged into the incorrect partition. And would remain there until a
full reprocess of the Fact tables are cube partitiions.
I understand that when data slices are defined for a partition, that AS2000
will only visit the partitions that contain the relevent data. What happens
if we use data slices in our situation?
If the data is in the incorrect partition, as a result of merging, is it
possible that the data is missed out, depending on the query? Or would the
merging not be allowed to take place?
Would we want to define data slices for all partitions, except for the
current month ?
Or would we want to define a filter, and rather than a build and merge, do
an incremental build against the cube ?
We are really looking for the quickest load time possible, while having
correct data of course.
Thanks in advance for any advice and help.Is this for AS 2000 or 2005? In 2000, slices that you defined are used for
querying. In 2005, slices for MOLAP partitions are detected automatically.
For ROLAP partitions the slice you specify will be used to eliminate
partitions from queries.
But besides that, only your latest partition would have "incorrect" data,
right? So just don't set a slice for that last partition -- it would mean
that this partition would be unnecessarily scanned for some queries, but it
should be a fairly small partition anyway...
Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Al" <Al@.discussions.microsoft.com> wrote in message
news:370E22EC-6304-49F2-B01D-812B37B2E1EF@.microsoft.com...
> Hi,
> we have cubes partitioned on the time dimension. The majority of data that
> comes in if for the last 4 days or so. The new data is loaded into a
> seperate
> fact table, and loaded into a partition. This partition is then merged
> into
> the current month partition.
> However, we can have data that comes in which is a month or longer old. So
> would be merged into the incorrect partition. And would remain there until
> a
> full reprocess of the Fact tables are cube partitiions.
> I understand that when data slices are defined for a partition, that
> AS2000
> will only visit the partitions that contain the relevent data. What
> happens
> if we use data slices in our situation?
> If the data is in the incorrect partition, as a result of merging, is it
> possible that the data is missed out, depending on the query? Or would the
> merging not be allowed to take place?
> Would we want to define data slices for all partitions, except for the
> current month ?
> Or would we want to define a filter, and rather than a build and merge, do
> an incremental build against the cube ?
> We are really looking for the quickest load time possible, while having
> correct data of course.
> Thanks in advance for any advice and help.
>sql

Monday, March 19, 2012

Data Partitioning (SQL Server 2005)

Hi,
Assuming that I have partitioned my Orders table based on OrderDate column
on 4 disks (each quarter on separate disk), do I need 4 processors for real
parallelism?
I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
resultset in parallel by having one CPU, or necessarily I must have one a
separate CPU for each disk?
Thanks in advance,
Leila"Leila" <Leilas@.hotpop.com> wrote in message
news:#kFBVxrVHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Assuming that I have partitioned my Orders table based on OrderDate column
> on 4 disks (each quarter on separate disk), do I need 4 processors for
> real parallelism?
> I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
> resultset in parallel by having one CPU, or necessarily I must have one a
> separate CPU for each disk?
Only computers that have more than one CPU can use parallel queries.
Degree of Parallelism
http://msdn2.microsoft.com/en-us/library/ms188611.aspx
However there's no rule that you have to put each partition on its own disk.
Instead you could store all or some of your partitions on a filegroup that
spans all the disks. Then every query will use all the disks evenly.
David

Data Partitioning (SQL Server 2005)

Hi,
Assuming that I have partitioned my Orders table based on OrderDate column
on 4 disks (each quarter on separate disk), do I need 4 processors for real
parallelism?
I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
resultset in parallel by having one CPU, or necessarily I must have one a
separate CPU for each disk?
Thanks in advance,
Leila
"Leila" <Leilas@.hotpop.com> wrote in message
news:#kFBVxrVHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Assuming that I have partitioned my Orders table based on OrderDate column
> on 4 disks (each quarter on separate disk), do I need 4 processors for
> real parallelism?
> I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
> resultset in parallel by having one CPU, or necessarily I must have one a
> separate CPU for each disk?
Only computers that have more than one CPU can use parallel queries.
Degree of Parallelism
http://msdn2.microsoft.com/en-us/library/ms188611.aspx
However there's no rule that you have to put each partition on its own disk.
Instead you could store all or some of your partitions on a filegroup that
spans all the disks. Then every query will use all the disks evenly.
David

Data Partitioning (SQL Server 2005)

Hi,
Assuming that I have partitioned my Orders table based on OrderDate column
on 4 disks (each quarter on separate disk), do I need 4 processors for real
parallelism?
I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
resultset in parallel by having one CPU, or necessarily I must have one a
separate CPU for each disk?
Thanks in advance,
Leila"Leila" <Leilas@.hotpop.com> wrote in message
news:#kFBVxrVHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Hi,
> Assuming that I have partitioned my Orders table based on OrderDate column
> on 4 disks (each quarter on separate disk), do I need 4 processors for
> real parallelism?
> I mean if a query needs the orders of 3 quarters, will 3 disks prepare the
> resultset in parallel by having one CPU, or necessarily I must have one a
> separate CPU for each disk?
Only computers that have more than one CPU can use parallel queries.
Degree of Parallelism
http://msdn2.microsoft.com/en-us/library/ms188611.aspx
However there's no rule that you have to put each partition on its own disk.
Instead you could store all or some of your partitions on a filegroup that
spans all the disks. Then every query will use all the disks evenly.
David

Data Partioning...Urgent help req.

hi,
i have a table with 5 million records which has been horizonatal partitioned
into 3 tables. Each of these tables are located on different logical drives
using filegroups in the same database on a single SQL Server Instance.
Each table structure is:
InvID int Identity,
InvDate datetime
InvNumber varchar(50)
InvID and InvDate together form the primary key for each of the tables.
InvDate is my partioning column...which partitions my data according to
years.
I have then created a view using a select * and union all clause which
selects the data from these tables.
When querying data selectively i.e. for a specific year using the partioned
view i get a performance gain of around 10 % over selection of data from a
non partioned view i.e. from a single table containing all the 5 million
records.
Is their someway in which i can get a better performance gain, as i feel 10%
gain is not much...some of the articles i have read claim that we can get a
performance gain of upto 35%.
I also do not get any performance gain when loading data or when firing a
select * i.e. selecting all 5 million records.
Can i get performace gain in these through the concept of horizontal
partitioning.
Plz help.
Regards,
Harman.Harman,
I don't see any value in partitioning the table with such small amount of
data. I am a bit surprised that you saw any performance gain. If you have
much larger amount of data, and you have multiple physical drives, you are
more likely to see the performance benefit especially when multiple
partition elements are hit. The administrative overhead far far outstrip
any (if any) performance improvement even if you want the fastest response
with such small amount of data.
Better solution in your case would be using a clustered covering index --
here basically putting all the three columns into the index.
hth
Quentin
"Harman Dhillon" <harmand@.grapecity.com> wrote in message
news:#d5wIoVcDHA.2632@.TK2MSFTNGP12.phx.gbl...
> hi,
> i have a table with 5 million records which has been horizonatal
partitioned
> into 3 tables. Each of these tables are located on different logical
drives
> using filegroups in the same database on a single SQL Server Instance.
> Each table structure is:
> InvID int Identity,
> InvDate datetime
> InvNumber varchar(50)
> InvID and InvDate together form the primary key for each of the tables.
> InvDate is my partioning column...which partitions my data according to
> years.
> I have then created a view using a select * and union all clause which
> selects the data from these tables.
> When querying data selectively i.e. for a specific year using the
partioned
> view i get a performance gain of around 10 % over selection of data from a
> non partioned view i.e. from a single table containing all the 5 million
> records.
> Is their someway in which i can get a better performance gain, as i feel
10%
> gain is not much...some of the articles i have read claim that we can get
a
> performance gain of upto 35%.
>
> I also do not get any performance gain when loading data or when firing a
> select * i.e. selecting all 5 million records.
> Can i get performace gain in these through the concept of horizontal
> partitioning.
> Plz help.
> Regards,
> Harman.
>
>
>
>

Sunday, March 11, 2012

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