the short version of my question is: "During the merge of two partitions is the whole partitioned table being blocked. How can I access, for read&write, the part that is not affected by the merge?"
And the context:
I am working on quite common scenario - there is a production table that grows in linear way (a few hundreds rows every 5 minutes). But we do not have to keep historical data online, so we decided to use partitioning to "bite" the historical tail from the database and move it to another one.
There are many tutorials how to achieve this goal - create partition function, scheme, partition the table with it's indices, switch the partition to identical empty table and so on. And if I want to do this one more time, the "standard" way is to split the current partition and merge it's tail with the historical one.
Now what I want to end up with (example): The table T is partitioned by some integer column P. Lets say there are at the moment three partitions and three filegroups. The partition function is set to split by values 30 and 60. The current value of the "counter" that produces values of the P column is 50. So there are some rows in partition 1 (HISTORY) which is now read-only, some rows in partition 2 (PRIMARY) that gets the periodical inserts and empty partition 3 (FUTURE).
Let the value of the counter be 70. Now the partition 1 and 2 are in the same situation. They are both read-only. The inserts are incoming with P value belonging to partition 3 only. And now I want to merge partitions 1 and 2 and move the result to another database. But I can see that during the merge of these two partitions all operations with the table T are being blocked. Even if it is INSERT to the third partition or there is selection predicate matching the partitioning function. How can I set-up the scheme or something else to unblock SELECT/INSERT with P value from partition 3 only (that is out of the game with respect to the merge)?