Yeah, fear of automation does bug me, it's almost as bad as "we've always done it like that" as a cause for irritating me!

That shouldn't happen, as the pk from the main table which the partitioning will be based upon will always be in the tables referencing it, so we will partition those...

I suppose the other advantage of creating the view on the view is that if future patches/upgrades change the behaviour again, then it's a small matter of changing the intermediate...

Will do, and thanks for all the comments so far. From conversations I've been having with the dev team alongside this post, it looks like we will be restructuring...

OK yeah that would work as a workaround; though it does add another layer of abstraction from the code being run.

Is there any information on how the optimizer...

BrainDonor (4/2/2015)

---

I don't understand why you need to drop any Foreign Keys before the Partition Switch, or are other tables referring to this data? If that is so, then shouldn't...

I believe the same applies with unique indexes also; though I haven't tried to know. I think this would be because there is a b-tree for each partition, but uniqueness...

ScottPletcher (4/1/2015)

---

All excellent points.

Keep in mind though:

1) You can leave the PK solely on the SmsID column and still cluster the table by ( date, id ). ...

Yes, that's the clustering choice I would have made; in fact I would have made that the pk. However, this is already in existence and has been rolled out...

Aha, light bulb moment! I knew I was missing something there, as much as anything from skim reading rather than properly looking at it.

So, if we were splitting into...

No I still don't understand; unless I've completely misunderstood, in the scenario you provide the value on the row changes, meaning that value now should be in a different partition...

BrainDonor (4/1/2015)

---

Matthew Darwin (4/1/2015)

---

Archiving and index maintenance are the two reasons I've been given, though I have an inkling the real reason might simply be wanting to try it out.

However,...

Archiving and index maintenance are the two reasons I've been given, though I have an inkling the real reason might simply be wanting to try it out.

However, I suspect that...

OK thanks; wasn't sure if that would work or would only evaluate for the old style package call.

OK thanks, will have a good read.