Split partition function slow because of no index on partition column

In slide windows, we want to move one month data from fast disk to the file group on slow disk. If there is no index on partition column as left most column of index, the split partition function will be slow. And during the splitting, it will lock the table. The solution to this:
 1. create a index on partition column. But if the table is huge, it will take long time to build it. And it cause the transaction log file growing to very huge.
2. The alternative way is to swtich out one more month data into staging table. So there are two partitions(months) having no data. To merge and split these empty partitions will be very fast.

DECLARE @date DATETIME

SELECT @date = GETDATE()

DECLARE @partValue VARCHAR(10)

DECLARE @priorMonth VARCHAR(10)

SELECT @priorMonth = CONVERT(VARCHAR(25), DATEADD(mm, -7, DATEADD(dd, -(DAY(@date)) + 1 , @date)), 101)

SELECT @partValue = CONVERT(VARCHAR(25), DATEADD(mm, -6, DATEADD(dd, -(DAY(@date)) + 1 , @date)), 101)

 --using script to move data from fast disk to test_staging slow disk, not include in this script

DECLARE @partNum int

SELECT @partNum = $partition.testPF(@partValue)

DECLARE @priorPartNum int

SELECT @priorPartNum = $partition.testPF(@priorMonth)

--switch out data to temp table

ALTER TABLE dbo.test SWITCH PARTITION @partNum TO dbo.test_temp --fast disk

ALTER TABLE dbo.test SWITCH PARTITION @priorPartNum TO dbo.test_staging2 -- on slow disk

--merge an split these empty partitions

ALTER PARTITION FUNCTION [testPF] ()

MERGE RANGE (@partValue)--Split the partition by creating a new partition in slow disk file group

ALTER PARTITION SCHEME testPS

NEXT USED slowFG

ALTER PARTITION FUNCTION [testPF] ()

--swith back the data

ALTER

TABLE dbo.test_staging SWITCH TO dbo.test PARTITION @partNum --on slow disk, this is the actual partition need to be moved from fast to slow disk

ALTER TABLE dbo.test_staging2 SWITCH TO dbo.test PARTITION @priorPartNum --on slow disk, this is just to fast split

 

 SPLIT RANGE (@partValue)