help on partition

i have a table named table1 sitting on a a partittion scheme px.

table1 is partitioned on u_id by tens,

first partion u_id=0 to 9

second partion u_id=10 to 19

third partion u_id=20 to 29

ans so on and so forth

i have a table named table2 with

records having u_id = 13 to 16

obviously belonging to the second partion.

i want to load table2 to table1 as fast as i could with

the following requirements:

all contents of partition 2 of table1 must be deleted before loading

table2 ceases to exist after the operation

how will i do that.

thanks.

The most efficient way is to do the following:

1. Use ALTER TABLE SWITCH to switch out partition #2 into unpartitioned table (table3)

2. Use ALTER TABLE SWITCH to switch in table2 into partition #2

3. Drop tables table2, table3

The following BOL topic has details on the switching strategies:

http://msdn2.microsoft.com/en-us/library/ms191160.aspx

And please check out the following whitepaper too.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5partition.asp

Help needed with Partitioning

We have a data warehouse which is updated monthly. We need to partition the
tables of the data warehouse in to filegroups for 1 month each.
These are the details of the schema:
Table: MonthDim
Columns: MonthId, MonthName, Year
Table: Fact1
Columns: Fact1Id, MonthId, Value
Table: Fact2
Columns: Fact2Id, Fact1Id, Value
Relation between Fact1 and Fact2 is 1-many.
We need to implement partitioning by creating filegroups which contain
monthly data only, i.e. Rows of that month from Fact1 and rows of those
Fact1Ids from Fact2.
Is this possible? As I see we need to specify the range for partitioning
while creating partitioned tables, whereas what we need is increasing the
partitioned files as data comes in for each month.
Also, we need to remove data after 2 years, that is the filegroup older than
24 months has to be removed, so the no of filegroups always remains at 24
max.
Thanks in advance.
Sumit PilankarTake a look at the ALTER PARTITION SCHEME command since this allows you to
add and remove filegroups. You'll also want the NEXT USED option to
designate which FG is used by the next partition that is created.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Sumit Pilankar" <sumit.pilankar@.gmail.com> wrote in message
news:eeZRkLBdGHA.3556@.TK2MSFTNGP02.phx.gbl...
> We have a data warehouse which is updated monthly. We need to partition
> the tables of the data warehouse in to filegroups for 1 month each.
>
> These are the details of the schema:
>
> Table: MonthDim
> Columns: MonthId, MonthName, Year
>
> Table: Fact1
> Columns: Fact1Id, MonthId, Value
>
> Table: Fact2
> Columns: Fact2Id, Fact1Id, Value
>
> Relation between Fact1 and Fact2 is 1-many.
>
> We need to implement partitioning by creating filegroups which contain
> monthly data only, i.e. Rows of that month from Fact1 and rows of those
> Fact1Ids from Fact2.
>
> Is this possible? As I see we need to specify the range for partitioning
> while creating partitioned tables, whereas what we need is increasing the
> partitioned files as data comes in for each month.
>
> Also, we need to remove data after 2 years, that is the filegroup older
> than 24 months has to be removed, so the no of filegroups always remains
> at 24 max.
>
> Thanks in advance.
> Sumit Pilankar
>
>

Help needed with Partitioning

We have a data warehouse which is updated monthly. We need to partition the
tables of the data warehouse in to filegroups for 1 month each.
These are the details of the schema:
Table: MonthDim
Columns: MonthId, MonthName, Year
Table: Fact1
Columns: Fact1Id, MonthId, Value
Table: Fact2
Columns: Fact2Id, Fact1Id, Value
Relation between Fact1 and Fact2 is 1-many.
We need to implement partitioning by creating filegroups which contain
monthly data only, i.e. Rows of that month from Fact1 and rows of those
Fact1Ids from Fact2.
Is this possible? As I see we need to specify the range for partitioning
while creating partitioned tables, whereas what we need is increasing the
partitioned files as data comes in for each month.
Also, we need to remove data after 2 years, that is the filegroup older than
24 months has to be removed, so the no of filegroups always remains at 24
max.
Thanks in advance.
Sumit PilankarTake a look at the ALTER PARTITION SCHEME command since this allows you to
add and remove filegroups. You'll also want the NEXT USED option to
designate which FG is used by the next partition that is created.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Sumit Pilankar" <sumit.pilankar@.gmail.com> wrote in message
news:eeZRkLBdGHA.3556@.TK2MSFTNGP02.phx.gbl...
> We have a data warehouse which is updated monthly. We need to partition
> the tables of the data warehouse in to filegroups for 1 month each.
>
> These are the details of the schema:
>
> Table: MonthDim
> Columns: MonthId, MonthName, Year
>
> Table: Fact1
> Columns: Fact1Id, MonthId, Value
>
> Table: Fact2
> Columns: Fact2Id, Fact1Id, Value
>
> Relation between Fact1 and Fact2 is 1-many.
>
> We need to implement partitioning by creating filegroups which contain
> monthly data only, i.e. Rows of that month from Fact1 and rows of those
> Fact1Ids from Fact2.
>
> Is this possible? As I see we need to specify the range for partitioning
> while creating partitioned tables, whereas what we need is increasing the
> partitioned files as data comes in for each month.
>
> Also, we need to remove data after 2 years, that is the filegroup older
> than 24 months has to be removed, so the no of filegroups always remains
> at 24 max.
>
> Thanks in advance.
> Sumit Pilankar
>
>

Help needed in Table partition

Hi,
I'm doing horizontal partition with partition key as an
integer datatype.It's a part of primary key also.When i
search for a particular value, it's fetching correct
results ,but if we examine execution plan all the
partitions are searched for a particular search.
Please help me to fetch the correct result in execution
plan
Thanks in advance,
Sunish
is your primary key clustered? Are you saying the the execution plan does a
table scan?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Sunish" <sunish_007@.hotmail> wrote in message
news:732801c4761d$d3435170$a501280a@.phx.gbl...
> Hi,
> I'm doing horizontal partition with partition key as an
> integer datatype.It's a part of primary key also.When i
> search for a particular value, it's fetching correct
> results ,but if we examine execution plan all the
> partitions are searched for a particular search.
> Please help me to fetch the correct result in execution
> plan
> Thanks in advance,
> Sunish
|||hi,
yea, the primary key is a clustered one.When i checked
the execution p[lan it shows Clustered Index Scan for
each partition ......also instead of compute saclar it's
showing Concatenation ,Cost=2%.
Thanks in advance
Sunish

>--Original Message--
>is your primary key clustered? Are you saying the the
execution plan does a
>table scan?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Sunish" <sunish_007@.hotmail> wrote in message
>news:732801c4761d$d3435170$a501280a@.phx.gbl...
>
>.
>
|||hi,
yea, the primary key is a clustered one.When i check
the execution plan it shows Clustered Index Scan for
each partition ......also instead of compute saclar it's
showing Concatenation ,Cost=2%.
Thanks in advance
Sunish

>--Original Message--
>is your primary key clustered? Are you saying the the
execution plan does a
>table scan?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Sunish" <sunish_007@.hotmail> wrote in message
>news:732801c4761d$d3435170$a501280a@.phx.gbl...
>
>.
>
|||hi,
yea, the primary key is a clustered one.When i check
the execution plan it shows Clustered Index Scan for
each partition ......also instead of compute saclar it's
showing Concatenation ,Cost=2%.
Thanks in advance
Sunish

>--Original Message--
>is your primary key clustered? Are you saying the the
execution plan does a
>table scan?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Sunish" <sunish_007@.hotmail> wrote in message
>news:732801c4761d$d3435170$a501280a@.phx.gbl...
>
>.
>
|||hi,
yea, the primary key is a clustered one.When i check
the execution plan it shows Clustered Index Scan for
each partition ......also instead of compute saclar it's
showing Concatenation ,Cost=2%.
Thanks in advance
Sunish

>--Original Message--
>is your primary key clustered? Are you saying the the
execution plan does a
>table scan?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Sunish" <sunish_007@.hotmail> wrote in message
>news:732801c4761d$d3435170$a501280a@.phx.gbl...
>
>.
>
|||I think this is normal.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
<anonymous@.discussions.microsoft.com> wrote in message
news:873601c47855$f7adc3d0$a601280a@.phx.gbl...[vbcol=seagreen]
> hi,
> yea, the primary key is a clustered one.When i checked
> the execution p[lan it shows Clustered Index Scan for
> each partition ......also instead of compute saclar it's
> showing Concatenation ,Cost=2%.
> Thanks in advance
> Sunish
>
> execution plan does a
|||hi,
Since it has to search for the corresponding partition
only rather than searching the entire partition views ,i
think i am wrong some where .
also in the execution plan ,it has to show compute scalar
instead of concatenation, as i referred to articles which
i collected from the net.
please inform if there is any other way i need to perform
this .
my query is like this only
select * from detailstable where programcode =222
[vbcol=seagreen]
>--Original Message--
>I think this is normal.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:873601c47855$f7adc3d0$a601280a@.phx.gbl...
it's[vbcol=seagreen]
as an[vbcol=seagreen]
also.When i[vbcol=seagreen]
execution
>
>.
>