Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Sub partitions Expand / Collapse
Posted Saturday, September 21, 2013 4:42 AM

SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:29 AM
Points: 889, Visits: 862
sf.onlineforums (9/21/2013)
Loading data by date, every week, is the main reason for redesigning partitions to be based on dates. I like your idea of generating an artificial key. Do you recommend partitioning the table on artificial key or just creating indexes would do the same thing?

I don't recommend anything, because I know too little of your system and your requirements. I'm just tossing up ideas. They may be right, or they may be a complete disaster.

What I can say is that using an artificial key will make your system more complex and add maintenance costs, and may also be a burden to users, if they access the database and will have to work with this key. But if this solution is needed to get acceptable performance for both loading and querying data, you may be prepared to pay the price.

And, oh, speaking about tossing up ideas, SQL Server is able to work with partitioning in two levels, since there are both partitioned tables and partitioned views. You could have one table per supplier, each table partioned by date. Then you would have a view which unites the supplier tables, so it looks like a single table. I need to add the caveat that I have no idea how well the optimizer is able to handle the combination. And, again this is a complex solution which will increase the total cost of ownership for the system.

Erland Sommarskog, SQL Server MVP,
Post #1497133
Posted Saturday, September 21, 2013 6:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 2:57 PM
Points: 6, Visits: 16
Fair enough.

I'm going to partition the table by date and use partition switching to expedite the process of loading data. The only caveat is that I have to drop the non-clustered index (supplier), before switching partitions as it's not aligned
Post #1497136
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse