分区交换作为SQL Server中的发布技术？ [英] Partition Exchange as publishing technique in SQL Server?

问题描述

我熟悉使用Oracle中的分区作为发布表的增量添加（在DW上下文中）的技术的概念。

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

（例如以下示例）

例如。数据集市事实表的每日快照将在后台隐藏在表的分区中。例如，以date为分区键（1个分区表，只有一个分区）。一旦完成加载并验证了内容，就可以将其作为字典事件交换到真正的目标表（1个分区表，其中有很多分区）。

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.

SQL Server 2008是否可以使用相同类型的发布模型？

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

推荐答案

是的，下面是SQL的示例Server 2008企业版

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

按年份划分分区功能 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

将范围映射到文件组的分区方案。对于此示例，我将所有分区都映射到PRIMARY文件组。

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

事实表，按年份划分

/* 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

一些要测试的数据

/* 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

要测试

SELECT * FROM factTbl

返回

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

有关更多详细信息，请参见 msdn文章 。

For more details see the msdn article.

这篇关于分区交换作为SQL Server中的发布技术？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！