Partition Exchange as publishing technique in SQL Server?

Question

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 snapshot for a data mart fact table is loaded behind the scenes in a partition within a table. for example with date as the partition key (1 partitioned table, with only one partition). once the load is complete, and the contents are validated, the partition can be 'exchanged' into the true destination table (1 partitioned table, with many partitions) as a dictionary event.

Is this same type of publishing model possible with SQL Server 2008?

Answer 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.

Answer 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 */
CREATE PARTITION FUNCTION myPFun (int)
AS RANGE RIGHT FOR VALUES (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 */
CREATE PARTITION SCHEME myPRng
AS PARTITION myPFun
ALL TO ( [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 */
ALTER TABLE stageTbl SWITCH PARTITION 4 TO factTbl PARTITION 4;
GO

To test

SELECT * FROM factTbl

Returns

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

For more details see the msdn article.