MSSQL动态分区方案

题记：这里的动态创建是指根据某一数据表中的字段值的多少动态的设置文件组、次要数据库文件、分区函数的分区范围、分区方案对应的文件组的设置。仅供参考

--配置数据库 启用 XP_CMDSHELL 命令

SP_CONFIGURE 'XP_CMDSHELL' , 1;

GO

RECONFIGURE

GO

IF EXISTS (SELECT NAME FROM SYS.SYSOBJECTS WHERE NAME = 'usp_CreatePartition' AND OBJECTPROPERTY(ID,'IsProcedure')=1 )

  DROP PROCEDURE usp_CreatePartition

GO

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

-- Author : MarcoLiu

-- Data   : 2012-06-12 10:29:12.627

-- Version: Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)   

--          Nov 24 2008 13:01:59   

--          Copyright (c) 1988-2005 Microsoft Corporation  

--          Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 

-- Subject: 动态创建分区方案

-- Param  : @AFileName 文件名称，该文件名称是指文件的前缀，系统产生的文件名为：@AFileName的值_流水号 如：MyFile_01 MyFile_02……

--          @AGroupName SQL文件组名 同 @AFileName 的产生效果

--          @FilePath 产生后的次要数据库文件的存放目录 暂时只支持同一目录，还没想好动态目录的存储

--          @BDelExists 是否删除已经存在的文件名、文件组、分区函数、分区方案，删除后将重新创建。取值：1 删除 0 不删除   

--          @BOnlyDel 只做删除，不作删除重建  1是 0否 

-- History: 2012-06-14 删除参数@DBName，增加参数@BOnlyDel。@DBName的获取改在过程里面获取，这样防止在这个数据下创建存储过程后创建那个数据库的存储方案，造成混乱

-- Memo   : 脚本中应用了2个分区方案，一个是根据数值，一个是根据日期

--          根据数值：这里只涉及到固定的几个数值，暂时不考虑大数值按数值范围分区

--          根据日期：这里的日期只根据日期的年份进行分区，同样不考虑固定日期范围的分区

-- Example: usp_CreatePartition 'Pubs', 'MyFile', 'MyGroup', 'D:\Test\', 1

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

CREATE PROCEDURE usp_CreatePartition(

     @AFileName Varchar(50)

   , @AGroupName Varchar(50)

   , @FilePath Varchar(200)

   , @BDelExists Bit

   , @BOnlyDel Bit

)

As

Begin

  Declare @DBName Varchar(200), @ErrorMsg Varchar(100), @RangVal Varchar(2000), @CmdResult Int, @CmdStr NVarchar(500)

  Declare @Year Int, @PartNum Int, @Tmp_FileName Varchar(100), @Tmp_GroupName Varchar(100), @PartNum_CNT INT, @YEAR_CNT INT, @Tmp_I Int

  --@Tmp_Table 存储需要创建的文件名和组名

  Declare @Tmp_Table Table(IID Int Identity(1,1), PartNum INT, sYear Varchar(10), sGroupName Varchar(100), sFileName Varchar(100))

  --调用外部CMD命令检查文件夹是否存在

  SET @DBName = DB_NAME()  --获取当前数据库名

  -- ===================================准备数据、验证数据=================================== --

  Set @CmdStr = 'CD ' + @FilePath

  EXEC @CmdResult = XP_CMDSHELL @CmdStr

  IF @CmdResult <> 0

  BEGIN

    Set @ErrorMsg = '文件夹 '+@FilePath+' 不存在!'

    GOTO ErrorHandle

  END

  --从 TableName 表中取得 PartNum 和 PartDate 存入到临时表中

  IF OBJECT_ID('TEMPDB.DBO.#TMP_PartNum') IS NOT NULL

    DROP TABLE #TMP_PartNum

  IF OBJECT_ID('TEMPDB.DBO.#TMP_PartDate') IS NOT NULL

    DROP TABLE #TMP_PartDate

  SELECT DISTINCT PartNum INTO #TMP_PartNum FROM TableName ORDER BY PartNum ASC

  SELECT DISTINCT SUBSTRING(PartDate, 0, CHARINDEX('-', PartDate)) AS PartDate INTO #TMP_PartDate FROM TableName ORDER BY PartDate

  --文件组必须比分区值多一个范围 

  SELECT @PartNum_CNT=COUNT(1)+1 FROM #TMP_PartNum

  SELECT @YEAR_CNT =COUNT(1)+1 FROM #TMP_PartDate

  IF @PartNum_CNT > @YEAR_CNT 

  Begin

     INSERT INTO @Tmp_Table(sGroupName, sFileName)

     SELECT @AGroupName+'_'+RIGHT(POWER(10, LEN(@PartNum_CNT)+1)+NUMBER, LEN(@PartNum_CNT)+1), @AFileName+'_'+RIGHT(POWER(10, LEN(@PartNum_CNT)+1)+NUMBER, LEN(@PartNum_CNT)+1)

     FROM MASTER..SPT_VALUES WHERE TYPE='P' AND NUMBER BETWEEN 1 AND @PartNum_CNT

  End

  Else 

  Begin

     INSERT INTO @Tmp_Table(sGroupName, sFileName)

     SELECT @AGroupName+'_'+RIGHT(POWER(10, LEN(@YEAR_CNT)+1)+NUMBER, LEN(@YEAR_CNT)+1), @AFileName+'_'+RIGHT(POWER(10, LEN(@YEAR_CNT)+1)+NUMBER, LEN(@YEAR_CNT)+1)

     FROM MASTER..SPT_VALUES WHERE TYPE='P' AND NUMBER BETWEEN 1 AND @YEAR_CNT

  End

  --判断数据库是否存在

  IF NOT EXISTS (SELECT NAME FROM SYS.DATABASES WHERE NAME=@DBName)

  Begin

    Set @ErrorMsg = '数据库 '+@DBName+' 不存在!'

    GOTO ErrorHandle

  End

  

  --检查判断分区方案 PS_HRPartitionByPartNum

  IF EXISTS (SELECT NAME FROM SYS.PARTITION_SCHEMES WHERE NAME='PS_HRPartitionByPartNum' AND TYPE_DESC='PARTITION_SCHEME')

  Begin

    IF @BDelExists = 1 OR @BOnlyDel = 1

      DROP PARTITION SCHEME PS_HRPartitionByPartNum

    ELSE 

    Begin

      Set @ErrorMsg = '分区方案 PS_HRPartitionByPartNum 已经存在，不能重新创建！'

      GOTO ErrorHandle

    End

  End

  --检查判断分区方案 PS_HRPartitionByPartDate

  IF EXISTS (SELECT NAME FROM SYS.PARTITION_SCHEMES WHERE NAME='PS_HRPartitionByPartDate' AND TYPE_DESC='PARTITION_SCHEME')

  Begin

    IF @BDelExists = 1 OR @BOnlyDel = 1

      DROP PARTITION SCHEME PS_HRPartitionByPartDate

    ELSE 

    Begin

      Set @ErrorMsg = '分区方案 PS_HRPartitionByPartDate 已经存在，不能重新创建！'

      GOTO ErrorHandle

    End

  End

  --检查判断分区函数 PF_HRPartitionByPartNum

  IF EXISTS (SELECT NAME FROM SYS.PARTITION_FUNCTIONS WHERE NAME='PF_HRPartitionByPartNum' AND TYPE_DESC='RANGE')

  Begin

    IF @BDelExists = 1 OR @BOnlyDel = 1 

      DROP PARTITION FUNCTION PF_HRPartitionByPartNum

    ELSE 

    Begin

      Set @ErrorMsg='分区函数 PF_HRPartitionByPartNum 已经存在，不能重新创建！'

      GOTO ErrorHandle

    End

  End

  --检查判断分区函数 PF_HRPartitionByPartNum

  IF EXISTS (SELECT NAME FROM SYS.PARTITION_FUNCTIONS WHERE NAME='PF_HRPartitionByPartDate' AND TYPE_DESC='RANGE')

  Begin

    IF @BDelExists = 1 OR @BOnlyDel = 1

      DROP PARTITION FUNCTION PF_HRPartitionByPartDate

    ELSE 

    Begin

      Set @ErrorMsg = '分区函数 PF_HRPartitionByPartDate 已经存在，不能重新创建！'

      GOTO ErrorHandle

    End

  End

  -- ===================================动态创建文件和文件组=================================== --

  DECLARE Cur_Temp CURSOR FOR 

    SELECT sFileName, sGroupName FROM @Tmp_Table

  OPEN Cur_Temp

  FETCH NEXT FROM Cur_Temp INTO @Tmp_FileName, @Tmp_GroupName

  WHILE @@FETCH_STATUS = 0

  BEGIN

    --检查判断次要数据库文件

    IF EXISTS (SELECT NAME FROM SYS.SYSFILES WHERE NAME = @Tmp_FileName)

    Begin

      IF @BDelExists = 1 OR @BOnlyDel = 1

        Exec ('ALTER DATABASE '+@DBName+' REMOVE FILE '+@Tmp_FileName)

      ELSE 

      Begin

        Set @ErrorMsg = '次要数据库文件 '+@Tmp_FileName+' 已经存在，不能重新创建！'

        GOTO ErrorHandle

      End

    End

    --检查判断文件组

    IF EXISTS (SELECT NAME FROM SYS.FILEGROUPS WHERE NAME = @Tmp_GroupName AND TYPE_DESC='ROWS_FILEGROUP')

    Begin

      IF @BDelExists = 1 OR @BOnlyDel = 1 

        Exec ('ALTER DATABASE '+@DBName+' REMOVE FILEGROUP '+@Tmp_GroupName)

      ELSE 

      Begin

        Set @ErrorMsg = '文件组 '+@Tmp_GroupName+' 已经存在，不能重新创建！'

        GOTO ErrorHandle

      End

    End

    IF @BOnlyDel = 0

    BEGIN

      --为分区创建文件组

      Exec ('ALTER DATABASE '+@DBName+' ADD FILEGROUP '+@Tmp_GroupName)

      IF @@ERROR <> 0 

      Begin 

        Set @ErrorMsg = '创建文件组 '+@Tmp_GroupName+' 时出错.'

        GOTO ErrorHandle

      End

      --为文件组设置存储文件

      Exec ('ALTER DATABASE '+@DBName+' ADD FILE (NAME = '''+@Tmp_FileName+''', FILENAME = '''+@FilePath+@Tmp_FileName+'.NDF'') TO FILEGROUP '+@Tmp_GroupName)

      IF @@ERROR <> 0 

      Begin 

        Set @ErrorMsg = '为文件组创建存储文件 '+@Tmp_FileName+' 时出错.'

        GOTO ErrorHandle

      End

    END

    FETCH NEXT FROM Cur_Temp INTO @Tmp_FileName, @Tmp_GroupName

  END

  CLOSE Cur_Temp

  DEALLOCATE Cur_Temp

  -- ===================================动态分区函数=================================== --

  IF @BOnlyDel = 0

  BEGIN

    SET @RangVal = ''

    SELECT @RangVal=@RangVal+','+CAST(PartNum AS VARCHAR) FROM #TMP_PartNum 

    SET @RangVal = RIGHT(@RangVal, LEN(@RangVal)-1)

    IF ISNULL(@RangVal, '') <> '' 

    BEGIN

      EXEC ('

        CREATE PARTITION FUNCTION PF_HRPartitionByPartNum(INT)

        AS 

          RANGE LEFT FOR VALUES ('+@RangVal+')')

      IF @@ERROR <> 0 

      Begin 

        Set @ErrorMsg = '创建分区函数 PF_HRPartitionByPartNum 时出错.'

        GOTO ErrorHandle

      End

    END

    ELSE 

    BEGIN

      Set @ErrorMsg = '创建分区函数 PF_HRPartitionByPartNum 时出错：分区值为空'

      GOTO ErrorHandle

    END

    SET @RangVal = ''

    SELECT @RangVal=@RangVal+','''+CAST(PartDate AS VARCHAR)+'-12''' FROM #TMP_PartDate

    SET @RangVal = RIGHT(@RangVal, LEN(@RangVal)-1)

    IF ISNULL(@RangVal, '') <> '' 

    BEGIN

      EXEC ('

        CREATE PARTITION FUNCTION PF_HRPartitionByPartDate(VARCHAR(8))

        AS

          RANGE LEFT FOR VALUES ('+@RangVal+')')

      IF @@ERROR <> 0 

      Begin 

        Set @ErrorMsg = '创建分区函数 PF_HRPartitionByPartDate 时出错.'

        GOTO ErrorHandle

      End

    END

    ELSE

    BEGIN

      Set @ErrorMsg = '创建分区函数 PF_HRPartitionByPartDate 时出错：分区值为空'

      GOTO ErrorHandle

    END

  END

  -- ===================================动态分区方案=================================== --

  IF @BOnlyDel = 0

  BEGIN

    SET @Tmp_GroupName = ''

    SELECT @Tmp_GroupName=@Tmp_GroupName+','+sGroupName From @Tmp_Table WHERE IID <= @PartNum_CNT

    SET @Tmp_GroupName = RIGHT(@Tmp_GroupName, LEN(@Tmp_GroupName)-1)

    IF ISNULL(@Tmp_GroupName, '') <> '' 

    BEGIN

      Exec ('

        CREATE PARTITION SCHEME PS_HRPartitionByPartNum

        AS 

          PARTITION PF_HRPartitionByPartNum TO ('+@Tmp_GroupName+')')

      IF @@ERROR <> 0 

      Begin 

        Set @ErrorMsg = '创建分区方案 PS_HRPartitionByPartNum 时出错.'

        GOTO ErrorHandle

      End

    END

    ELSE

    BEGIN

      Set @ErrorMsg = '创建分区方案 PS_HRPartitionByPartNum 时出错：文件组不存在.'

      GOTO ErrorHandle

    END

    SET @Tmp_GroupName = ''

    SELECT @Tmp_GroupName=@Tmp_GroupName+','+sGroupName From @Tmp_Table WHERE IID <= @YEAR_CNT

    SET @Tmp_GroupName = RIGHT(@Tmp_GroupName, LEN(@Tmp_GroupName)-1)

    IF ISNULL(@Tmp_GroupName, '') <> '' 

    BEGIN

      Exec ('

        CREATE PARTITION SCHEME PS_HRPartitionByPartDate

        AS 

          PARTITION PF_HRPartitionByPartDate TO ('+@Tmp_GroupName+')')

      IF @@ERROR <> 0 

      Begin 

        Set @ErrorMsg = '创建分区方案 PS_HRPartitionByPartDate 时出错.'

        GOTO ErrorHandle

      End

    END

    ELSE

    BEGIN

      Set @ErrorMsg = '创建分区方案 PS_HRPartitionByPartDate 时出错：文件组不存在.'

      GOTO ErrorHandle

    END

  END

  -- ===================================分区完成及错误=================================== --

  Print '分区方案创建成功！'

  ErrorHandle:

    IF EXISTS (SELECT CURSOR_NAME FROM SYS.SYSCURSORS WHERE CURSOR_NAME='Cur_Temp')

    Begin

      CLOSE Cur_Temp

      DEALLOCATE Cur_Temp

    End

    IF OBJECT_ID('TEMPDB.DBO.#TMP_PartNum') IS NOT NULL

      DROP TABLE #TMP_PartNum

    IF OBJECT_ID('TEMPDB.DBO.#TMP_PartDate') IS NOT NULL

      DROP TABLE #TMP_PartDate

    Print '错误信息：'+@ErrorMsg 

End

GO