创建分区表

--创建建立分区函数,分区方案存储过程

 

CREATE PROCEDURE [dbo].[Proc_Create_Subarea]

    @year INT

AS

    DECLARE @date DATETIME,@date_string varchar(max);

    DECLARE @cmd VARCHAR(MAX);

   

    SELECT

        @date = DATEADD(year,@year-1900,0),   --当年的第一天

        @date_string = '';

   

    SELECT

        @date_string =

             @date_string + ','''+

                  CONVERT(VARCHAR(10),DATEADD(day,number,@date),120)+''''

    FROM master.dbo.spt_values

    WHERE type='p' AND number <=366

        AND YEAR(DATEADD(day,number,@date)) = @year;

       

    SET @date_string = STUFF(@date_string,1,1,'');

   

    SET @cmd = 'CREATE PARTITION FUNCTION [PF_BY_DATE_'+RTRIM(@year)+'](datetime)

                 AS RANGE RIGHT FOR VALUES

                 ('+@date_string+')';

                

    EXEC (@cmd);

   

    SET @cmd = 'CREATE PARTITION FUNCTION [PF_BY_SMALLDATETIME_'+RTRIM(@year)+'](smalldatetime)

                AS RANGE RIGHT FOR VALUES

                ('+@date_string+')';

               

    EXEC(@cmd);

 

    SET @cmd = 'CREATE PARTITION SCHEME [PS_BY_DATE_'+RTRIM(@year)+']

                AS PARTITION [PF_BY_DATE_'+RTRIM(@year)+']

                ALL TO ([PRIMARY])';

               

    EXEC(@cmd);

   

    SET @cmd = 'CREATE PARTITION SCHEME [PS_BY_SMALLDATE_'+RTRIM(@year)+']

                AS PARTITION [PF_BY_SMALLDATETIME_'+RTRIM(@year)+']

                ALL TO ([PRIMARY])';

               

    EXEC(@cmd);

GO

 

--创建年的分区函数与方案

EXEC [dbo].[Proc_Create_Subarea] 2009

GO

 

--创建测试表.使用PS_BY_DATE_2009分区方案

CREATE TABLE tb(id INT IDENTITY,date DATETIME,

                  dzbz NUMERIC(2,1),fssd NUMERIC(2,1),

                  dzxh VARCHAR(6),ylxh INT,com VARCHAR(4),

                CONSTRAINT PK_ID_date PRIMARY KEY CLUSTERED(id,date))

          ON PS_BY_DATE_2009(date);

 

 

INSERT tb(date,dzbz,fssd,dzxh,ylxh,com)

    SELECT TOP(100000) --这里设置多少行

        DATEADD(day,(ABS(CHECKSUM(NEWID())) % 365 ),'2009-01-01') AS Date,

        (ABS(CHECKSUM(NEWID())) % 5 + 4) / 10. AS dzbz,

        (ABS(CHECKSUM(NEWID())) % 3 + 12) / 10. AS fssd,

        'DZ_00' + RTRIM(CASE WHEN ROW_NUMBER() OVER(ORDER BY o.object_id) % 4 = 0

                          THEN 4 ELSE ROW_NUMBER() OVER(ORDER BY o.object_id) % 4 END) AS dzxh,

        1 AS ylxh,

        CASE WHEN ((ROW_NUMBER() OVER(ORDER BY o.object_id)-1) / 4 % 4) %2 = 0

                THEN 'com1' ELSE 'com2' END AS com

    FROM sys.columns AS c

        CROSS JOIN sys.objects AS o

        CROSS JOIN sys.types AS t;

--

 

GO

 

--查看每个分区的记录数

SELECT

    $PARTITION.PF_BY_DATE_2009(date) AS [part],

    COUNT(*) AS rows

FROM tb

GROUP BY $PARTITION.PF_BY_DATE_2009(date)

ORDER BY part;