Shulou(Shulou.com)06/01 Report--

-- dynamically generate SQL SERVER database partition scripts according to time (date). After partitioning, you can manually associate with the table.

-- generate partition scripts

DECLARE @ DataBaseName NVARCHAR (50)-- Database name

DECLARE @ TableName NVARCHAR (50)-- Table name

DECLARE @ ColumnName NVARCHAR (50)-- Field name

How many zones does DECLARE @ PartNumber INT-- need to be divided into

DECLARE @ Location NVARCHAR (50)-the path where the partition file is saved

DECLARE @ Size NVARCHAR (50)-Partition initialization size

DECLARE @ FileGrowth NVARCHAR (50)-Partition File Increment

-- DECLARE @ FunValue INT-- partition segment value

DECLARE @ I INT

DECLARE @ y INT-start year

DECLARE @ m INT-start month

DECLARE @ d INT-start day

DECLARE @ dayGap INT-the number of days of partition partition value

DECLARE @ PartNumberStr NVARCHAR (50)

DECLARE @ sql NVARCHAR (max)

DECLARE @ ValueStart NVARCHAR (50)

-- set the following variables

SET @ DataBaseName = 'HEJIEXUN'

SET @ TableName = 'DYNAMINE_A_REAL_DATA'

SET @ ColumnName = 'TIME'

SET @ PartNumber = 500

SET @ Location ='C:\ HejiexunServer\ Database\'

SET @ Size = '80MB'

SET @ FileGrowth ='30%'

SET @ y = 2015

SET @ m = 1

SET @ d = 1

SET @ dayGap = 3

-1. Create a filegroup

SET @ I = 1

PRINT'- 1. Create filegroup'

WHILE @ I 30 OR (@ m = 2 AND @ d > 28)-February needs special treatment

BEGIN

SET @ d = 1

SET @ m = @ m + 1

If (@ m > 12)

BEGIN

SET @ m = 1

SET @ y = @ y + 1

END

END

IF (@ I = @ PartNumber-1)

BEGIN

SET @ FunValueStr = substring (@ FunValueStr,1,len (@ FunValueStr)-1)

END

PRINT @ FunValueStr

SET @ FunValueStr =''

SET @ i=@i+1

END

PRINT')'+ CHAR (13)

-- 4. Create a partition scheme

PRINT CHAR (13) +'- 4. Create a partition scheme'

DECLARE @ FileGroupStr NVARCHAR (MAX)

-- SET @ FileGroupStr = substring (@ FileGroupStr,1,len (@ FileGroupStr)-1)

SET @ sql = 'CREATE PARTITION SCHEME

Sch_'+@TableName+'_'+@ColumnName+' AS

PARTITION Fun_'+@TableName+'_'+@ColumnName+'

TO ('

PRINT @ sql

SET @ I = 1

SET @ FileGroupStr =''

WHILE @ I

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.