sqlenthu 89358 (8/26/2016)
I wanted to do the same Matt and as you clarified, I get it that it's not possible. Rather I am taking a new approach now. The whole purpose was to insert records parallel or fastest way possible. So here is my plan:Create a partition function with date as partition
Create a partition scheme with all on primary
Create the target table as partitioned over the partitioned scheme
Now run the processing procedures in parallel to populate data into temp tables for different years
And at end switch table to target partitioned table.
Do you see any loophole in this ?
No loopholes but I still see problems. You're still loading tables and there's a really good chance that they are still being written to the same drive (or striped drives) as the others. I think that adding partitioning is OK if you're going to keep the year-data and you could benefit from greatly reduced index maintenance and maybe even greatly reduced backup requirements if you set the older static years to Read_Only (assuming 1 year per filegroup which also means 1 file per filegroup, in this case), but it's probably not going to be a panacea of performance for doing the initial load because [font="Arial Black"]you just can't change physics[/font]. If you can't guarantee the use of more than 1 read/write head (or 1 set of striped heads), that's going to be the bottleneck no matter how many parallel loads you try and, because of the additional head thrashing it will cause, it's actually likely to be slower than a straight forward serial load.
--Jeff Moden
Change is inevitable... Change for the better is not.