创建sql server分区表

联系:手机(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题:创建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
此条目发表在 SQL Server 分类目录。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>