创建sql server分区表

1、创建数据库

USE Master 
GO
CREATE DATABASE Test_Partitioning
ON PRIMARY
(NAME='Partitioning_1',
FILENAME=
'E:\database\partitions\Partitioning_1.mdf',
SIZE=4,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP FG2
(NAME = 'Partitioning_2',
FILENAME =
'E:\database\partitions\Partitioning_2.mdf',
SIZE = 4,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP FG3
(NAME = 'Partitioning_3',
FILENAME =
'E:\database\partitions\Partitioning_3.mdf',
SIZE = 4,
MAXSIZE=100,
FILEGROWTH=1 )
GO

2、创建分区函数

Use test_Partitioning 
GO
CREATE PARTITION FUNCTION salesYearPartitions (datetime)
AS RANGE RIGHT FOR VALUES ( '2009-01-01', '2010-01-01')
GO

说明：
RIGHT:表示”=”在右边
LEFT:表示”=”在左边

3、创建分区方案

Use test_Partitioning 
GO
CREATE PARTITION SCHEME Test_PartitionScheme
AS PARTITION salesYearPartitions
TO ([PRIMARY], FG2, FG3 )
GO

4、使用分区创建表

Use test_Partitioning 
GO
CREATE TABLE SalesArchival
(SaleTime datetime PRIMARY KEY,
ItemName varchar(50))
ON Test_PartitionScheme (SaleTime);
GO

5、验证SQL语句
5.1)确定文件组的数量和数据库数据文件的数量

Use test_Partitioning 
GO
-- Confirm Filegroups
SELECT name as [File Group Name]
FROM sys.filegroups
WHERE type = 'FG'
GO 
-- Confirm Datafiles
SELECT name as [DB File Name],physical_name as [DB File Path]
FROM sys.database_files
where type_desc = 'ROWS'
GO

5.2)验证分区表上的数据分布

Use test_Partitioning 
GO
select partition_id, index_id, partition_number, Rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='SalesArchival'
GO