谁有那种按天将表分区的脚本??做成一个存储过程直接运行的。。一年分成365个区。。求一个完美的分区脚本。。
我自己写的脚本好像有问题
CREATE PROCEDURE [dbo].[Proc_Create_Subarea]@year dateAS
BEGINDECLARE @date date
,@date_string varchar(1024)
set @year= dateadd(day, 1-day(@year), @year); --当年01日
SET @date_string = ''
set @date = @year
while @date < dateadd(month, 6, dateadd(MONTH, 1-month(@year), @year)) begin
set @date_string = @date_string + '''' + convert(char(10), @date, 120) + ''','
set @date = dateadd(day, 1, @date)
end
set @date_string = left(@date_string, len(@date_string) - 1) EXEC('
CREATE PARTITION FUNCTION [PF_by_date](date) AS RANGE RIGHT FOR VALUES (' + @date_string + '); CREATE PARTITION FUNCTION [PF_by_smalldatetime] (smalldatetime) AS RANGE RIGHT FOR VALUES (' + @date_string + '); CREATE PARTITION SCHEME [PS_by_date] AS PARTITION [PF_by_date] ALL TO ([FG_Stat_Data]); CREATE PARTITION SCHEME [PS_by_smalldatetime] AS PARTITION [PF_by_smalldatetime] ALL TO ([FG_Stat_Data]); ')
END
我自己写的脚本好像有问题
CREATE PROCEDURE [dbo].[Proc_Create_Subarea]@year dateAS
BEGINDECLARE @date date
,@date_string varchar(1024)
set @year= dateadd(day, 1-day(@year), @year); --当年01日
SET @date_string = ''
set @date = @year
while @date < dateadd(month, 6, dateadd(MONTH, 1-month(@year), @year)) begin
set @date_string = @date_string + '''' + convert(char(10), @date, 120) + ''','
set @date = dateadd(day, 1, @date)
end
set @date_string = left(@date_string, len(@date_string) - 1) EXEC('
CREATE PARTITION FUNCTION [PF_by_date](date) AS RANGE RIGHT FOR VALUES (' + @date_string + '); CREATE PARTITION FUNCTION [PF_by_smalldatetime] (smalldatetime) AS RANGE RIGHT FOR VALUES (' + @date_string + '); CREATE PARTITION SCHEME [PS_by_date] AS PARTITION [PF_by_date] ALL TO ([FG_Stat_Data]); CREATE PARTITION SCHEME [PS_by_smalldatetime] AS PARTITION [PF_by_smalldatetime] ALL TO ([FG_Stat_Data]); ')
END
我用的SQL2008
@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--创建2009年的分区函数与方案
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;