【笔记】SQLSERVER表分区

----sqlserver2008表分区脚本-------------------------

--创建时间: 2009-11-10 10:13
--创建人  : 周伟
----------------------------------------------------

----------------------参数定义----------------------

Declare @partitionFunction AS Nvarchar(50) --分区函数名称
Declare @partitionScheme AS Nvarchar(50)   --分区架构名称
Declare @partitionField AS Nvarchar(50)    --按那个字段分区
Declare @partitionTable AS Nvarchar(50)    --要对那个表分区
Declare @IndexName      AS Nvarchar(50)    --聚集索引名称

--分区函数名称
Set @partitionFunction='ByTime'
--分区架构名称
Set @partitionScheme='ByTime'

---聚集索引名称
set @IndexName='kpi_base_msc_Index'

--按那个字段分区
Set @partitionField='scantime'
--要对那个表分区
Set @partitionTable='kpi_base_msc'

----------------------------------------------------

-----------------------SQL脚本----------------------

     
----■表分区创建

     --●创建分区函数
         if exists (select 1 from sys.partition_functions where name='my_pfun')
         drop partition function my_pfun
        
         create partition function my_Pfun(datetime)as range left for
         VALUES (N'2009-11-06T00:00:00.000', N'2009-12-06T00:00:00.000', N'2010-01-06T00:00:00.000', N'2010-02-06T00:00:00.000', N'2010-03-06T00:00:00.000', N'2010-04-06T00:00:00.000', N'2010-05-06T00:00:00.000', N'2010-06-06T00:00:00.000', N'2010-07-06T00:00:00.000', N'2010-08-06T00:00:00.000', N'2010-09-06T00:00:00.000', N'2010-10-06T00:00:00.000', N'2010-11-06T00:00:00.000', N'2010-12-06T00:00:00.000', N'2011-01-06T00:00:00.000')

     --●创建分区架构
          if exists (select 1 from sys.partition_schemes where name='my_pSch')
         
          CREATE PARTITION SCHEME [my_pSch]AS PARTITION my_pfun TO ( [FG1], [FG2], [FG3], [FG4], [FG5], [FG6], [FG7], [FG8], [FG9], [FG10],[FG11],[FG12],[FG13],[FG14],[FG15],[FG16], [PRIMARY] )
       
     --●对表进行分区
          
           ----创建表并且对该表指定分区架构
            --CREATE TABLE customers (FirstName nvarchar(40), LastName nvarchar(40), CustomerNumber int,scantime DateTime) ON ByTime(scantime)
           
           ----对现有表进行分区    
   Alter TABLE  dbo.kpi_base_msc WITH NOCHECK ADD
   CONSTRAINT [dbo.kpi_base_msc] PRIMARY KEY CLUSTERED
   (
     [scantime]
   )
   ON [ByTime]([scantime])
   

----■分区表的维护（拆分、合并和移动）
   
 --●拆分 
    Alter PARTITION FUNCTION pf_RegMail() split RANGE ('20090101 00:00:00');
      
 --●合并
    Alter PARTITION FUNCTION pf_RegMail() MERGE RANGE ('20080101 00:00:00');
   
 --●移动
   
     
    
----■其它

    --●查询每个分区表的记录条数
      
   
    --●查询记录的所在组
       select *,$partition.ByTime(scantime) AS 所在组 from dbo.kpi_base_cell where scantime='2099-01-01 00:00:00.000'
   
    --●如果原来的表里有主键哪就要执行下面语句:
       alter table dbo.kpi_base_chgr drop constraint kpi_base_chgr2--将表的主键删除
      
    --●为分区创建存储文件,根据月份来划分文件组：
        Alter DATABASE TopDb ADD FILEGROUP RegMailFile2009
      
    --●为文件组设置存储文件
            ALTER DATABASE TopDb
   ADD FILE
   (NAME = N'Month1',FILENAME = N'C:\data\fg500.ndf',SIZE = 5MB,MAXSIZE = 1000MB,FILEGROWTH = 5MB)
   TO FILEGROUP [fg500];
   

----■测试数据导入脚本

  --测试数据导入SQL(一千万条数据)
  declare @NowDate AS Datetime
  declare @Id      As int
  set @Id=1;
  set @NowDate='2009-11-06 23:59:59';
  while @Id<10000000
  begin
  Insert into dbo.kpi_base_msc(scantime,id,name) values (CONVERT(varchar(20),@NowDate,120) ,@Id+1,'Name')
  set @Id=@Id+1;
  set @NowDate=DateAdd( ss,5 , @NowDate);
  end
  
------------------------------------------------------------------------------------------