首页 / 数据库 / SQLServer / sql server大数据归档

sql server大数据归档2014-03-14 csdn博客 ocpyang昨天做了个日常大数据归档，归档700W数据，表字段130左右，字段比较多，分享下！

---- 先禁用表的index

1.先获取需要禁用的索引

declare @tname varchar（100）

set @tname="orders"

select  "alter index "+" "+c.indexname+" "+"on"+" "+@tname+" "+"disable"

from

（

select * from

（

SELECT

OBJECT_NAME（i.OBJECT_ID） AS TableName,

i.name AS IndexName,

i.index_id AS IndexID,

8 * SUM（a.used_pages）/1024 AS "Indexsize（MB）"

FROM sys.indexes AS i

JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id

JOIN sys.allocation_units AS a ON a.container_id = p.partition_id

GROUP BY i.OBJECT_ID,i.index_id,i.name

）a

where a.tablename=@tname

--order by [Indexsize（MB）] desc

）c

go

--2.禁止上面语句 获得索引，但是主键和clustered index别禁用，切记！

----删除数据

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

go

SET NOCOUNT ON

BEGIN TRANSACTION

while 1=1

begin

delete top（20000） from dbo.orders   with（TABLOCK）

where ordertime <"2010-1-1"

if @@rowcount<20000

break

end

commit

go

----索 引重建

alter index all on orders rebuild

go

基本上很短时间搞定，为了性能，需 要完成索引rebuild和统计信息更新！

查看本栏目更多精彩内容：http://www.bianceng.cn/database/SQLServer/