Partition Exchange As Publishing Technique In Sql Server?

I'm familiar with the concept of using partitions in Oracle as a technique to pubish incremental additions to tables (in a DW context). (like this example) For example. a daily sn

Solution 1:

Table partitioning is available in the Developer and Enterprise editions of the SQL Server product and will enable you to do that process - to do it automated with stored procs etc is a bit harder but is achievable.

Solution 2:

Yes, and here is an example for SQL Server 2008 enterprise

Partition function by year 1:= Y < 2008, 2:= 2008, 3:= 2009, 4:= 2010, 5:= Y >= 2011

/* First create a partition function */CREATEPARTITIONFUNCTION myPFun (int)
ASRANGERIGHTFORVALUES (20080101, 20090101, 20100101, 20110101);
GO

Partition scheme to map ranges to file-groups. For this example I will map all partitions to the PRIMARY file group.

/* Then a partition scheme */CREATEPARTITION SCHEME myPRng
ASPARTITION myPFun
ALLTO ( [PRIMARY] ); 
GO

And a fact table, partitioned by year

/* Fact table partitioned by year */
CREATE TABLE factTbl(DateKey int, Value int)
ON myPRng(DateKey) ;
GO

Staging table, partitioned the same way

/* Staging table partitioned the same way as the fact table */
CREATE TABLE stageTbl(DateKey int, Value int)
ON myPRng(DateKey) ;
GO

Some data to test

/* Populate fact table (years 2008, 2009)*/
INSERT  INTO factTbl
        ( DateKey, Value )
VALUES  ( 20080205, 10 )
,       ( 20080711, 25 )
,       ( 20090525, 43 );

/* Populate staging table (year 2010) */
INSERT  INTO stageTbl
        ( DateKey, Value )
VALUES  ( 20100107, 10 );

And switch the partition from the staging table to the fact table

/* From staging table to fact table */ALTERTABLE stageTbl SWITCH PARTITION4TO factTbl PARTITION4;
GO

To test

SELECT*FROM factTbl

Returns

DateKey     Value
----------- -----------
20080205    10
20080711    25
20090525    43
20100107    10

For more details see the msdn article.