Can you post the CREATE TABLE statement (including indexes, keys, FKs)? That might help a bit here.

Just so you know, partitioning a table that has other tables pointing to it via FK can be a real pain because, in order for a column to be unique on such a table, either the partitioning column must be added to the otherwise unique column (which would break it as an FK candidate) or the UNIQUE index on the column can't be "aligned" which would kill the ability to do SWITCHes to move legacy data out of the table quickly.

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T."--22 Aug 2013

Except for the dr_sk column, I don't see any other unique indexes on this table and that's a good thing for paritioning-sake.

What's the nature of the dr_frdatetime_hq column?1. Is it assigned a value as part of the insert of the row? If not, is there such a date column in the table (we need one to partition)?2. Is it (that column) ever updated?

The reason I ask is because (especially because of the indexes on this table) it looks like it might make a decent column to base monthly partitions on.

Also, (and I have to double-check when I get home) the existing constraints with the NO CHECK hint might be a problem for paritioning. IIRC, no constraints in a table can have the NO CHECK option if you want fully aligned partitions that can someday take advantage of SWITCH (out or in) but that might only apply to the parititioning column. Like I said, I have to double-echeck on that.

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T."--22 Aug 2013

If the rows eventually become static, there are some huge pitfalls with individual FileGroups having mounds of wasted space.... and individual FileGroups by month is definitely the way to go for maintenance especially where backups and piece-meal restores are concerned.

I've solved those pitfalls but it's not easy because of the problems associated with extra space generated during index rebuilds and the fact that DBCC SHRINKFILE hammers indexes for fragmentation.

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T."--22 Aug 2013