SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sub partitions


Sub partitions

Author
Message
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2111 Visits: 872
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, www.sommarskog.se
Sunil.Aggusher
Sunil.Aggusher
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search