Azure SQL Database (27) 创建Table Partition

　　《Windows Azure Platform 系列文章目录》

 

　　昨天客户正好提到这个问题，现在记录一下。

　　我们在使用传统的SQL Server，会使用Table Partition，这个功能在云端的Azure SQL Database也是可以实现的。

　　

　　1.首先我们创建一个Azure SQL Database数据库，过程略

　　2.使用SQL Server Management Studio链接

　　3.执行下面的TSQL

--Create Table
CREATE TABLE [dbo].[FactInternetSales]
(
    [ProductKey]             int          NULL
,   [OrderDateKey]           int          NULL
,   [CustomerKey]            int          NULL
,   [PromotionKey]           int          NULL
,   [SalesOrderNumber]       nvarchar(20) NULL
,   [OrderQuantity]          smallint     NULL
,   [UnitPrice]              money        NULL
,   [SalesAmount]            money        NULL
)


--CREATE Partition Function
CREATE PARTITION FUNCTION [pf_DayOfTheYear](INT) AS RANGE LEFT FOR VALUES 
 (20000101,20010101,20020101
                    ,20030101,20040101,20050101
                    )

--Creating a SQL Partition Scheme
CREATE PARTITION SCHEME [ps_DayOfTheYear] AS PARTITION [pf_DayOfTheYear] ALL TO ([PRIMARY])


--Show Partition
SELECT ps.name, pf.name, boundary_id, [value]
FROM sys.partition_schemes ps
INNER JOIN sys.partition_functions pf ON pf.function_id=ps.function_id
INNER JOIN sys.partition_range_values prf ON pf.function_id=prf.function_id


--Create Patition
CREATE CLUSTERED INDEX IX_TABLE1_OrderdateKey ON dbo.[FactInternetSales] (OrderDateKey)
  WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
  ON [ps_DayOfTheYear](OrderDateKey)
GO


--Test Data
INSERT INTO [dbo].[FactInternetSales](OrderDateKey) VALUES (20000101);
--多增加一行
INSERT INTO [dbo].[FactInternetSales](OrderDateKey) VALUES (20000101);

INSERT INTO [dbo].[FactInternetSales](OrderDateKey) VALUES (20010101);
INSERT INTO [dbo].[FactInternetSales](OrderDateKey) VALUES (20020101);
INSERT INTO [dbo].[FactInternetSales](OrderDateKey) VALUES (20030101);
INSERT INTO [dbo].[FactInternetSales](OrderDateKey) VALUES (20040101);
INSERT INTO [dbo].[FactInternetSales](OrderDateKey) VALUES (20050101);


-------------------------------- SHOW INDEXES with their partitions / row counts (only one to begin with) ------------------------------------------------------------------------
SELECT o.name objectname, i.name indexname, partition_id, partition_number, [rows] --, f.[name] 'FileGroup', i.data_space_id
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
--left outer join sys.filegroups f on i.data_space_id = f.data_space_id
WHERE o.name = 'FactInternetSales'