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