Based on what I have found here:
This is the most optimized way:
CREATE TABLE parent ... ;
CREATE TABLE child_1() INHERITS (parent);
CREATE TABLE child_2() INHERITS (parent);
INSERT commands are done always into child_1.
SELECT commands are done on parent.
On your priod of choice, run the follwing sql for rotating child_1 with child_2:
TRUNCATE TABLE ...
Let's try this query:
SELECT TOP 1000 A.id
FROM sys.partitions AS P
CROSS APPLY ( SELECT TOP 1000 T1.id
FROM dbo.table AS T1
WHERE $PARTITION.PF1(T1.date) = P.partition_number
AND T1.id > @certain_id
ORDER BY T1.id ) AS A
WHERE P.object_id = OBJECT_ID('dbo.table')
AND P.index_id = INDEXPROPERTY( ...