/*A.按数据时间字段进行分区*/
--1.创建数据库以及文件组和对应的文件('20170101','20170201','20170301','20170401')
--2.创建分区函数
CREATE PARTITION FUNCTION partfunSale (datetime)
AS RANGE RIGHT FOR VALUES ('20170101','20170201','20170301','20170401')
--3.创建分区方案
CREATE PARTITION SCHEME partschSale
AS PARTITION partfunSale
TO (
[primary],--这里要加上primary否则会提示错误
Sale20170101,
Sale20170201,
Sale20170301,
Sale20170401
)
--4.创建数据表
CREATE TABLE Sale(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](16) NOT NULL,
[SaleTime][datetime] NOT NULL
) ON partschSale([SaleTime])
/*B.按数据总条数进行分区*/
--创建数据库及分区文件
USE master
GO
IF DB_ID('Sample_DB') IS NOT NULL
DROP DATABASE [Sample_DB]
CREATE DATABASE [Sample_DB] ON PRIMARY
(
NAME =N'Sample_DB',FILENAME=N'E:\Sample_DB\Sample_DB.mdf',
SIZE=3072KB,FILEGROWTH=1024KB
), FILEGROUP [FG_1]
(
NAME =N'FG_1_DataFile',FILENAME=N'E:\Sample_DB\FG_1_DataFile.ndf',
SIZE=3072KB,FILEGROWTH=1024KB
), FILEGROUP [FG_2]
(
NAME =N'FG_2_DataFile',FILENAME=N'E:\Sample_DB\FG_2_DataFile.ndf',
SIZE=3072KB,FILEGROWTH=1024KB
), FILEGROUP [FG_3]
(
NAME =N'FG_3_DataFile',FILENAME=N'E:\Sample_DB\FG_3_DataFile.ndf',
SIZE=3072KB,FILEGROWTH=1024KB
), FILEGROUP [FG_N]
(
NAME =N'FG_N_DataFile',FILENAME=N'E:\Sample_DB\FG_N_DataFile.ndf',
SIZE=3072KB,FILEGROWTH=1024KB
) LOG ON
(
NAME =N'Sample_DB_log',FILENAME=N'E:\Sample_DB\Sample_DB_log.ldf',
SIZE=3072KB,FILEGROWTH=10%
)
GO
--创建一个带有RANGE LEFT的分区函数pf_OneMillion_LeftRange:
USE Sample_DB
GO
CREATE PARTITION FUNCTION pf_OneMillion_LeftRange(INT)
AS RANGE LEFT FOR VALUES(0,1000000,2000000,3000000)
GO
--验证分区函数
USE Sample_DB
GO
SELECT name ,
function_id ,
type ,
type_desc ,
fanout ,
boundary_value_on_right ,
create_date
FROM sys.partition_functions
SELECT function_id ,
boundary_id ,
parameter_id ,
value
FROM sys.partition_range_values
GO
--现在运行下面的脚本,创建并验证分区架构:
USE Sample_DB
GO
CREATE PARTITION SCHEME ps_OneMillion_LeftRange
AS PARTITION pf_OneMillion_LeftRange
TO ([primary],[FG_1],[FG_2],[FG_3],[FG_N])
SELECT name ,
data_space_id ,
type ,
type_desc ,
function_id
FROM sys.partition_schemes
GO
--现在创建一个表tbl_SampleRecords,并插入500万数据:
USE Sample_DB
GO
IF OBJECT_ID('tbl_SampleRecords') IS NOT NULL
DROP TABLE tbl_SampleRecords
CREATE TABLE tbl_SampleRecords
(
id INT ,
SomeDate SYSNAME ,
CONSTRAINT pk_tbl_SampleRecords_id PRIMARY KEY CLUSTERED ( id )
)
ON ps_OneMillion_LeftRange(id)
GO
INSERT INTO tbl_SampleRecords
SELECT TOP 5000000
id = ROW_NUMBER() OVER ( ORDER BY C1.name ) ,
somedata = c1.NAME
FROM sys.columns AS C1
CROSS JOIN sys.columns AS C2
CROSS JOIN sys.columns AS C3
GO
--验证一下分区数和行数
USE Sample_DB
GO
SELECT partition_id ,
object_id ,
index_id ,
partition_number ,
rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('tbl_SampleRecords')
GO
sqlserver 2008 r2 表分区
最新推荐文章于 2021-01-30 15:26:47 发布