Dual Column Partitioning

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Strategies » Dual Column Partitioning

Dual Column Partitioning

Rate Topic

Display Mode

Topic Options

Author

Message

bhushanvinay

Posted Friday, January 08, 2010 3:50 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, July 11, 2012 6:35 AM
Points: 329, Visits: 194

Need Help With Partitioning.

I have a Table

Create Table OrdersFromSystemsFeedTable
(
InternalOrderID,
SystemOrderID,
ExternalSystemName,
OrderDate,
IsProcessed,
)

Need to remove data which is 3 months old, Huh!! Dodle, easy to do this, Catch !! how to make sure that the data which is not processed which is older than 3 months is not archived via Paritioning.

Is there a way this can be done or I am barking the wrong tree.

Kind regards
Vinay

Feed Table is huge 30M Records and 5M Each month growth Stats.

Regards
Vinay

Post #844190

ALZDBA

Posted Friday, January 08, 2010 4:43 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:13 PM
Points: 6,866, Visits: 8,071

the query will provide the same results. partitioned or not.

If you are refering to the sliding window technique, off course, you should first check it the partition only contains data to be removed before doing it to a full partition.

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help

- How to prevent a sore throat after hours of presenting ppt ?

"press F1 for solution", "press shift+F1 for urgent solution" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #844206

bhushanvinay

Posted Friday, January 08, 2010 5:10 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, July 11, 2012 6:35 AM
Points: 329, Visits: 194

Let me Re - query the same,

The idea is to have 3 paritions for each month when we hit the limit then remove the last partition, Issue here is when we do that we dont want the Isprocessed Flag rows to be gone they should be still in the DB and available online.

Sliding window is what i am looking for But the Partition function can be done only on one column AFAIK.

Is there any Stratergy? which you guys are already using.

I can write a proc for purge, This table is a aggregation of different Orders from different vendors.

That gives a lot more control but its not fast as Sliding window Archiving process. How to do a Sliding window with conditional data? is there a way to do this.

Regards
Vinay

Post #844213

ALZDBA

Posted Friday, January 08, 2010 5:35 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:13 PM
Points: 6,866, Visits: 8,071

Don't shoot me if it doesn't perform as expected.

You could add a persisted derived column and partition on the combination of the flagprocessed and the stringconverted datetime combined..

Keep min mind this will shift rows from one partition to another whenever the flagprocessed is modified !

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #844215

Paul White

Posted Saturday, January 09, 2010 8:25 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 2:43 PM
Points: 10,990, Visits: 10,576

1. Using a (persisted) computed column would be my preference also. If the date portion is placed first in the compound field, you could arrange the partition function so that changing the processed status only results in a change of partition for rows older than 3 months. You would then just not archive partitions in the pre-3-month range which hold unprocessed rows. To minimize data movement when crossing the 3-month boundary, ensure that processed rows stay in the same partition as they age.

2. Assuming that there are relatively few unprocessed rows older than 3 months, another strategy might be to temporarily move the unprocessed rows older than 3 months to a temporary table or variable just before the partition is archived, and then re-insert them afterward.

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #844893

« Prev Topic | Next Topic »

Permissions