创建定时任务挺容易的 http://www.cnblogs.com/peaceshow/archive/2012/07/16/2593934.html脚本可以类似这样 TRUNCATE TABLE Table2INSERT INTO Table2 SELECT * FROM Table1 WHERE spyndate>=DATEADD(MONTH,-2,GETDATE())TRUNCATE TABLE Table1INSERT INTO Table1 SELECT * FROM Table2TRUNCATE TABLE Table2
假设分区函数和分区方案如下:CREATE PARTITION FUNCTION PF_PartitionedByMonth_1 (date) AS RANGE LEFT FOR VALUES ('2014-12-01', '2015-01-01', '2015-02-01', '2015-03-01')CREATE PARTITION SCHEME PS_PartitionedByMonth_1 AS PARTITION PF_PartitionedByMonth_1 TO ('PRIMARY', 'PRIMARY', 'PRIMARY', 'PRIMARY', 'PRIMARY')假设表的定义如下CREATE TABLE Table1 ( id int IDENTITY(1, 1) , name varchar(30) , gender bit , spyndate date CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED (id, spyndate)) ON PS_PartitionedByMonth_1 (spyndate)查询保留两个月数据以外的最近的分区号,语句如下:SELECT CASE pf.boundary_value_on_right WHEN 0 THEN p.partition_number - 1 ELSE p.partition_number - 2 END FROM sys.objects obj JOIN sys.indexes ind ON obj.object_id = ind.object_id JOIN sys.partition_schemes ps ON ind.data_space_id = ps.data_space_id JOIN sys.partition_functions pf ON ps.function_id = pf.function_id JOIN sys.partitions p ON ( obj.object_id = p.object_id AND ind.index_id = p.index_id ) LEFT JOIN sys.partition_range_values prv ON p.partition_number = prv.boundary_id WHERE prv.value = DATEADD(MM, -2, CONVERT(DATE, DATEADD(DD, -1 * DATEPART(DD, GETDATE()) + 1, GETDATE())))
忘了加表名条件了,补充一下:SELECT CASE pf.boundary_value_on_right WHEN 0 THEN p.partition_number - 1 ELSE p.partition_number - 2 END FROM sys.objects obj JOIN sys.indexes ind ON obj.object_id = ind.object_id JOIN sys.partition_schemes ps ON ind.data_space_id = ps.data_space_id JOIN sys.partition_functions pf ON ps.function_id = pf.function_id JOIN sys.partitions p ON ( obj.object_id = p.object_id AND ind.index_id = p.index_id ) LEFT JOIN sys.partition_range_values prv ON p.partition_number = prv.boundary_id WHERE obj.name = 'Table1' AND prv.value = DATEADD(MM, -2, CONVERT(DATE, DATEADD(DD, -1 * DATEPART(DD, GETDATE()) + 1, GETDATE())))
谢谢你的回复!你的分区方案是不是问题: CREATE PARTITION SCHEME PS_PartitionedByMonth_1 AS PARTITION PF_PartitionedByMonth_1 TO ('PRIMARY', 'PRIMARY', 'PRIMARY', 'PRIMARY', 'PRIMARY')你把所有的数据库都放在同一个文件组Primary,在切换分区时会不会把其他的分区数据也删除掉? 还是说: 分区与文件组在做切换分区时不会相互影响?
SQL Server中的分区是指表或索引的分区,是一个逻辑上的概念,从大到小的分级是这样的:表或索引 -> 分区(partition) -> 分配单元(allocation unit) -> 区(extent) -> 页(page)。 而文件组是一个物理上的概念,一个表的不同分区可以位于不同的文件组上,这样可以提高并发性能。分区方案就是用来指定,根据分区函数划分的若干个分区,分别位于哪些文件组上。我使用的测试环境,数据库test只有默认的PRIMARY文件组,所以就将分区表Table1的全部分区都放在PRIMARY上了。实际上,普通表也是有分区的,只不过只有一个。你用普通表Table2和Table1中的某一个分区进行交换,实际上只是把逻辑上的所属关系改变了,原来Table2的分区1“挂接”到了Table1上,而Table1的分区5(假设)“挂接”到了Table2上。但是交换完,两个分区的partition id是不变的,物理上的存储情况也不会改变。这也是为什么交换分区能在很短的时间内完成。另外,刚看了一下,昨天写的代码还是有点问题,月份错位了。SQL Server的分区概念比较复杂(相对于Oracle来说),分区方案有边界值靠左还是靠右的问题,特别容易搞错,呵呵。分区表不多的话,还是建议程序负责检查提醒,DBA手工来维护。你要是用程序来实现的话,一定要反复好好测试,这个要是错了事儿就大了~ 正确的查询语句如下:SELECT CASE pf.boundary_value_on_right WHEN 0 THEN p.partition_number ELSE p.partition_number - 1 END FROM sys.objects obj JOIN sys.indexes ind ON obj.object_id = ind.object_id JOIN sys.partition_schemes ps ON ind.data_space_id = ps.data_space_id JOIN sys.partition_functions pf ON ps.function_id = pf.function_id JOIN sys.partitions p ON ( obj.object_id = p.object_id AND ind.index_id = p.index_id ) LEFT JOIN sys.partition_range_values prv ON p.partition_number = prv.boundary_id WHERE obj.name = 'Table1' AND prv.value = DATEADD(MM, -2, CONVERT(DATE, DATEADD(DD, -1 * DATEPART(DD, GETDATE()) + 1, GETDATE())))
对你说的太对了。我忘记把一个背景条件说漏掉了:
还有表 Table2, 表的结构跟Table1一模一样,是Table1的备份表,但是我发现这个Table2是空的,既然是备份表为啥是空的呢,一直感觉很奇怪。原来用你的方法, Table2 就派上用场了。
那如何制定 作业,每天自动执行呢?
我想过写SQL语句来: 每天删除 2个月以前的Table1表的数据。但是这么做,不知道如何在任务计划中部署??你有什么好的方法?
http://www.cnblogs.com/peaceshow/archive/2012/07/16/2593934.html脚本可以类似这样
TRUNCATE TABLE Table2INSERT INTO Table2
SELECT * FROM Table1
WHERE spyndate>=DATEADD(MONTH,-2,GETDATE())TRUNCATE TABLE Table1INSERT INTO Table1
SELECT * FROM Table2TRUNCATE TABLE Table2
例如, 当前是3月,只保留2个月内的数据,那么4->12这8个表分区都是空的? 以此类推,总是多数分区是空的,查询性能也不佳.
请问是否真有必要?如果只想保留2个月内的数据,可以不必用分区表,并在时间字段上建索引即可.
用下面的语句效率才高吧
ALTER TABLE [Table1] SWITCH PARTITION 1 TO [Table2] PARTITION 1ALTER TABLE [Table1] SWITCH PARTITION 2 TO [Table2] PARTITION 2
表中只保存一年的数据,12个月,13个分区(包括主分区primary)
编写一个作业,每个月1号定时创建最近一个分区,并删除最早一个分区
(如到了2015-03-01 ,将删除2014-03-01的分区数据,并创建新的分区保存2015-03的数据)删除的办法和思路是:
1. 增加新的分区,当前月份用(SPLIT RANGE)
2. 切换该表最早的分区(primary)到另一个结构相同的表(SWITCH PARTITION 1 TO )
3. 切换后,把最早的分区合并( MERGE RANGE)
4. 把切换出来的表数据清空(truncate table 很快)
5. 更新统计信息(或者重建索引)(其实我这表还处于同步复制中,就不写了,思路这样,希望有帮助)
谢谢你的回复!但是我们这边不能创建分区,也不能删除原始表。
删除思路是这样,你可以改下。不用删除原来的表,是创建另一个表,表结构跟原表一样,这个表用来切换数据用。将最早的两个分区迁移到这个表,再truncate。切一个分区就truncate一次表。
前提是:
1 表 Table1 数据不能删除,所以不能使用该语句: truncate table Table1
2 保留 2 个月以内的数据,每个月的第一天执行作业脚本。以4月1日为例,保留2月份和3月份的数据,其他分区数据全部删除。现在碰到的问题是:
如何正确在SQL语句中把时间规划好? 尤其是在 2月1日时,要保留当年1月份的数据,和去年12月的数据。
现在我把脚本写好了,如下效果:truncate table Table2-----首先确认系统时间是几月份
DATEPART(month, GETDATE()) -----然后用当前的月份数字往前面减去1,减2,得到要保留的数据
------- 这个应该写个PL/SQL,但是自己不知道该如何去实现,请大家帮帮忙。注意要跨越2年的数据(在2月1日开始执行作业,那么去年12月的数据必须保留)。
-------
-----假设要保留数据的月份是M,N,分别对应的分区是 partition M, partition N,那么除了M,N外,其他分区的数据必须通过切换分区到 Table2 上去,删除掉。假设剩下8个月的数据分区是A, B, C,D,过程如下:alter table Table1 switch partition A to Table2 partition A
alter table Table1 switch partition B to Table2 partition B
.........
........truncate table Table2请大家帮帮忙,谢谢!
Table1里面的数据一直没有处理过,有从2003年到2015年 1月份到12月份的数据。如果下个月的第一天4月1日,开始执行任务---只保留最近2个月(2月份,3月份)的数据,那么会删除1月份,4月份,5月份,.......,12月的数据,对应删除分区1,分区4,分区5,........, 分区12 的数据。
但是,从此以后,每个月的第一天(如5月1日)只需要删除3个月以前的那个月的数据。
5月1日执行任务,只需要删除2月份的数据,因为当前只有2月份,3月份,4月份有数据
6月1日执行任务,只需要删除3月份的数据,
........
........
........
12月1日执行任务,只需要删除9月份的数据
1月1日执行任务,只需要删除10月份的数据
2月1日执行任务,只需要删除11月份的数据
3月1日执行任务,只需要删除12月份的数据
4月1日执行任务,只需要删除1月份的数据。
所以在写脚本时,要特别注意 1月1日,2月1日,3月1日执行任务时涉及的月份。
因此脚本如下:
#######################################################################
truncate table Table2
---这里首先要判断一下当前月份是不是 1,2,3 这三个数。如果是1,切换分区10;如果是2,切换分区11;如果是3,切换分区12;
---但是具体SQL 不知道该如何写,感觉这里不能纯粹写SQL就能行,应为还要判断月份--------》需要大家帮忙写下,谢谢
--- ......
---如果当前月份不是 1,2,3 的话,就执行下面的SQL
alter table Table1 switch partition (DATEPART(month, GETDATE())-3) to Table2 partition (DATEPART(month, GETDATE())-3)
truncate table Table2
#######################################################################
请帮忙看下,这个脚本该如何写呢?
谢谢!
据我了解,按月分区,如果是时间类型,也只是一段时间内而已,你说你们12个分区,也应该就是某一年的12个月而已,因此调动分区,还需要改分区范围
因此我需要知道你们是否是真的按时间类型分区,还是说是按由时间列计算出来的月份的计算列进行的分区?
还是有什么我没有了解的知识,也请科普一下
假设分区函数和分区方案如下:CREATE PARTITION FUNCTION PF_PartitionedByMonth_1 (date)
AS RANGE LEFT
FOR VALUES ('2014-12-01', '2015-01-01', '2015-02-01', '2015-03-01')CREATE PARTITION SCHEME PS_PartitionedByMonth_1
AS PARTITION PF_PartitionedByMonth_1
TO ('PRIMARY', 'PRIMARY', 'PRIMARY', 'PRIMARY', 'PRIMARY')假设表的定义如下CREATE TABLE Table1
(
id int IDENTITY(1, 1)
, name varchar(30)
, gender bit
, spyndate date CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED (id, spyndate)) ON PS_PartitionedByMonth_1 (spyndate)查询保留两个月数据以外的最近的分区号,语句如下:SELECT
CASE pf.boundary_value_on_right
WHEN 0 THEN p.partition_number - 1
ELSE p.partition_number - 2
END
FROM sys.objects obj
JOIN sys.indexes ind ON obj.object_id = ind.object_id
JOIN sys.partition_schemes ps ON ind.data_space_id = ps.data_space_id
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
JOIN sys.partitions p ON ( obj.object_id = p.object_id AND ind.index_id = p.index_id )
LEFT JOIN sys.partition_range_values prv ON p.partition_number = prv.boundary_id
WHERE prv.value = DATEADD(MM, -2, CONVERT(DATE, DATEADD(DD, -1 * DATEPART(DD, GETDATE()) + 1, GETDATE())))
CASE pf.boundary_value_on_right
WHEN 0 THEN p.partition_number - 1
ELSE p.partition_number - 2
END
FROM sys.objects obj
JOIN sys.indexes ind ON obj.object_id = ind.object_id
JOIN sys.partition_schemes ps ON ind.data_space_id = ps.data_space_id
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
JOIN sys.partitions p ON ( obj.object_id = p.object_id AND ind.index_id = p.index_id )
LEFT JOIN sys.partition_range_values prv ON p.partition_number = prv.boundary_id
WHERE obj.name = 'Table1'
AND prv.value = DATEADD(MM, -2, CONVERT(DATE, DATEADD(DD, -1 * DATEPART(DD, GETDATE()) + 1, GETDATE())))
谢谢你的回复!你的分区方案是不是问题:
CREATE PARTITION SCHEME PS_PartitionedByMonth_1
AS PARTITION PF_PartitionedByMonth_1
TO ('PRIMARY', 'PRIMARY', 'PRIMARY', 'PRIMARY', 'PRIMARY')你把所有的数据库都放在同一个文件组Primary,在切换分区时会不会把其他的分区数据也删除掉?
还是说: 分区与文件组在做切换分区时不会相互影响?
而文件组是一个物理上的概念,一个表的不同分区可以位于不同的文件组上,这样可以提高并发性能。分区方案就是用来指定,根据分区函数划分的若干个分区,分别位于哪些文件组上。我使用的测试环境,数据库test只有默认的PRIMARY文件组,所以就将分区表Table1的全部分区都放在PRIMARY上了。实际上,普通表也是有分区的,只不过只有一个。你用普通表Table2和Table1中的某一个分区进行交换,实际上只是把逻辑上的所属关系改变了,原来Table2的分区1“挂接”到了Table1上,而Table1的分区5(假设)“挂接”到了Table2上。但是交换完,两个分区的partition id是不变的,物理上的存储情况也不会改变。这也是为什么交换分区能在很短的时间内完成。另外,刚看了一下,昨天写的代码还是有点问题,月份错位了。SQL Server的分区概念比较复杂(相对于Oracle来说),分区方案有边界值靠左还是靠右的问题,特别容易搞错,呵呵。分区表不多的话,还是建议程序负责检查提醒,DBA手工来维护。你要是用程序来实现的话,一定要反复好好测试,这个要是错了事儿就大了~ 正确的查询语句如下:SELECT
CASE pf.boundary_value_on_right
WHEN 0 THEN p.partition_number
ELSE p.partition_number - 1
END
FROM sys.objects obj
JOIN sys.indexes ind ON obj.object_id = ind.object_id
JOIN sys.partition_schemes ps ON ind.data_space_id = ps.data_space_id
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
JOIN sys.partitions p ON ( obj.object_id = p.object_id AND ind.index_id = p.index_id )
LEFT JOIN sys.partition_range_values prv ON p.partition_number = prv.boundary_id
WHERE obj.name = 'Table1'
AND prv.value = DATEADD(MM, -2, CONVERT(DATE, DATEADD(DD, -1 * DATEPART(DD, GETDATE()) + 1, GETDATE())))