BobMcC (7/3/2014)
Yes, it's a DW environment. However our current business practice of how we load data and report off it don't lend itself to one of the perks of partitioning; which is Switching.
It can be a bit of a fudge but you might consider an archive table(s) without partitioning (or archive off a set amount each month/week/etc) it should save time on rebuilding indexes. Do you have the table index with 1billion rows in a separate filegroup? Do you always need to have all 1billions rows for business needs (ie reporting/MIS, etc)
BobMcC (7/3/2014)
Current procedure for loading data: monthly incoming data extracts are staged, then merged (inserted and updated) into production database. As soon as it loads our reporting process kicks off; and we begin simultaneously loading the next client.
It does sound a form of table/index paritioning could work for you, but like most things you never know until you test it. 😀
BobMcC (7/3/2014)
In the examples usually seen for partitioning it's always a fresh set of data that gets switched into the table. Data to be UPDATED can't be switched in, and since we have multiple clients, switching would only work for the first client in (unless we partitioned by the client number). Also we don't really get rid of any data either (except when a client is discontinued) so we don't really need to switch data out either.
But won't your table continue to grow and become unmanageable?
BobMcC (7/3/2014)
I don't really have any specific questions yet; I just wanted to get a dialog started on partitioning under my (unique?) circumstances.Do any of you have any experience with an environment similar to this? Partitioning by client just doesn't *seem* right. I just have it in my head to use some type of date but maybe that's just not applicable.
In the examples I have used in the past (and currently) I partition based on a datetime column.
Some handy tips here.
qh