Question on best practice - keep empty partitions at both ends of your table?

Question

I was reading ALTER PARTITION FUNCTION best practices which says -

Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) and partition merge (after unloading old data) do not incur any data movement. Avoid splitting or merging populated partitions. This can be extremely inefficient, as this may cause as much as four times more log generation, and may also cause severe locking.

Questions :

• Is it sufficient to ensure that there will be no data movement by ensuring that the new boundary corresponds to a future date or do I really need the empty partition ?
• I think certain optimizations are done if the partition is empty, but is it correct to say that the major cost is in the data movement itself?
• Is there anything new in SQL 2014 that could help out (we are moving from 2008 to 2014 .. yes I am aware that SQL Server 2019 is out ..but we will eventually get there) ?

I saw a lot on Managed Lock Priority (e.g. https://blogs.technet.microsoft.com/dataplatforminsider/2013/08/16/improved-application-availability-during-online-operations-in-sql-server-2014/); however, that seems to apply only to Table Partition Switch and Online Index Rebuild, but not to an ALTER PARTITITON FUNCTION.

Answer 1

Is it sufficient to ensure that there will be no data movement by ensuring that the new boundary corresponds to a future date or do I really need the empty partition ?

If your partition function is RANGE RIGHT, then yes. A partition split moves rows in the existing partition that belong in the new partition. The new partition is the partition that contains the new boundary value. So in RANGE RIGHT if the new boundary value is after all the rows in the table, no rows need be moved.

Hopefully you choose RANGE RIGHT to begin with so you don't have to have boundary values like 2015-12-31 11:59:59:999.

Depending on the indexes, the head partition may need to be scanned to discover that no rows need to be moved.