sqlserver 2008 r2 表分区

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