table partitioning

Question

table partitioning

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

ratheesh4sql (3/16/2013)
Based on the article from the link i hope we can do the table partition based on region into 3 partition and after a month we can archive into other three partition using Sliding Window Table Partitioning.....

At the end of a month, you delete an entire region? Really? Are you sure that's what's done?

You don't archive into other partitions with the sliding window, you archive into another *table* and delete that data from the main table completely.

But here am confused beginning itself to do the partition using Region by group wise
Region ID 1,2,3 = Partition1
Region ID 4,5,6 = Partition2
Region ID 7,8,9 = Partition3

With that partitioning, at the end of a month, you delete completely regions 1,2 and 3?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ratheesh4sql SSCommitted Points: 1811 More actions · Answer 2

I dont want to delete the data from the table i just want to archive into different partition since.

Here the table partition

A,B,C based on the region and then after one month need archive into D,E,F partition.

Is this is possible ?

So then A,B,C Partition will have only the latest data

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

That's not how partitioning works. Please go and do some reading on partitioning.

The sliding window method moves entire partitions from one table to another table. Emphasis on *move*. It's a quicker version of insert data into the archive table and delete from the main table.

Having data moving from one partition to another in one table should be avoided wherever possible.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

ratheesh4sql (3/13/2013)
planning to do the partitioning for the main table with region wise.....So that we can split the data into a different file group and the data accessible also will be more fast ?

Partitioning is primarily a data management tool. It can provide some performance benefits, but it usually doesn't. I would heed Kevin's advice and be sure you need this before you do it. It's a ton of labor to set up and will be even harder to break down if you don't see any benefits from it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning