Table Partition for date range>90 days

Question

Table Partition for date range>90 days

khushbu

SSCarpal Tunnel

Points: 4591
More actions
August 15, 2016 at 11:59 pm

#329346

Wanted to know how do I create partition function for date range >90 days
basically I need partition all the rows from Table A which are older than 90 days from today.
Also how do I make it dynamic so that it can run every 15 days for eg.
Thanks in advance for help

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 1

khushbu (8/15/2016)
Wanted to know how do I create partition function for date range >90 days
basically I need partition all the rows from Table A which are older than 90 days from today.
Also how do I make it dynamic so that it can run every 15 days for eg.
Thanks in advance for help

Google for "Sliding Window Example for Partitioning".

Shifting gears, why do you need to partition the table? If you're doing it for performance of code, forget about partitioning. Partitioning usually slows code down a bit because of the multiple B-Trees that are formed. A properly indexed monolithic table will almost always beat a partitioned table when it comes to performance even in light of so called "partition elimination", which relies on the very indexes that make monoliths so fast.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

khushbu (8/15/2016)
Wanted to know how do I create partition function for date range >90 days

You don't. Not exactly

Partition boundaries have to be fixed values, so you can have a partition on dates where the boundary is '2016-08-01 00:00:00', not a certain number of days in the past.

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

khushbu SSCarpal Tunnel Points: 4591 More actions · Answer 3

Thanks All.

Its sad that only hard coded date needs to be given. I was wandering of having a variable and scheduling a job so that I don't have to change every time. <sigh>

The partition is not for performance gain but I need to delete huge number of records (over billion) and tables have FK relationship. Normal Delete takes long long time, we also tried move required data to new table, drop old table, rename and create FK.

Of all these table partition was fastest

frederico_fonseca SSCoach Points: 16011 More actions · Answer 4

you can automate it - but depending on your volumes and what you do with the partition of over 90 days you may have performance issues.

assume a weekly process.

current table has 2 partitions (range right)

1 - 2016-04-01

2 -

Determine new date = equal today - 90 days (assume for example that resulting date is 2016-04-08)

do alter partion split for this date

Now there are 3 partitions on the table

1 - 2016-04-01

2 - 2016-04-08

3 -

do a merge partition of 1 and 2

Now there are 2 partitions on the table

1 - 2016-04-08

2 -

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

A strongly recommend against doing the above. It'll work, but not well. The guidelines for adding partitions is to ensure that no data needs to move from one partition to another. If you do a split where rows in one partition suddenly belong in a new partition, they have to be moved. It's essentially a delete/insert operation, which is exactly what you're trying to avoid doing by partitioning the table.

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