英文原文：
我Facebook上的一个朋友和老同事，他是一个财务产品的团队领导，问我他有一个几百万记录的表，想去从这个表归档一些记录，他该如何做？
简单的DELETE语句会产生大量的日志文件（如果数据库不是简单恢复模式），会锁住该表非常长时间，并消耗大量资源而极其影响性能。
通常保持简短的事务是非常好的，我总是喜欢批量DELETE/UPDATE记录，尤其是当有大量的记录在生产环境中，因为在达赖那个事务环境删除大量记录需要很长时间，如果你取消DELETE操作可能需要数小时或一天时间，一切将会被回滚，也将会花费很长时间，如果你小批量删除，像每次1000行，而你停止执行，你将只回滚最大1000行，而不用多长时间。
让我们通过创建一个示例数据库来展示如何实现。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
--Create sample database
CREATE
DATABASE
ExtremeAdvice
GO
USE ExtremeAdvice
GO
--create sample table along with 100,000 rows
IF OBJECT_ID(
'orders'
,
'U'
)
IS
NOT
NULL
BEGIN
DROP
TABLE
orders
END
GO
CREATE
TABLE
orders (OrderID
INT
IDENTITY, OrderDate DATETIME, Amount MONEY, Refno
INT
)
GO
INSERT
INTO
orders (OrderDate, Amount, Refno)
SELECT
TOP
100000
DATEADD(
minute
,
ABS
(a.OBJECT_ID % 50000 ),
CAST
(
'2012-02-01'
AS
DATETIME)),
ABS
(a.OBJECT_ID % 10),
CAST
(
ABS
(a.OBJECT_ID)
AS
VARCHAR
)
FROM
sys.all_objects a
CROSS
JOIN
sys.all_objects b
GO
IF OBJECT_ID(
'ordersArchive'
,
'U'
)
IS
NOT
NULL
BEGIN
DROP
TABLE
ordersArchive
END
GO
CREATE
TABLE
ordersArchive (OrderID
INT
, OrderDate DATETIME, Amount MONEY, Refno
INT
)
GO
|
现在我们已经准备好了示例环境，让我们创建一个存储过程，基于我们提供的条件删除记录。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
USE [ExtremeAdvice]
GO
CREATE
PROCEDURE
[dbo].[SPordersArchive]
AS
SET
NOCOUNT
ON
DECLARE
@cnt
INT
, @
rows
INT
SELECT
@cnt = 1
DECLARE
@msg
VARCHAR
(1024)
DECLARE
@dt DATETIME
--creating infinite loop which will break itself whenever all record gets deleted based on condition given
WHILE 1=1
BEGIN
SELECT
@dt = GETDATE(),
@
rows
= 0
--Deleting records in bunch
DELETE
TOP
(1000) o
OUTPUT
deleted.OrderID,
deleted.OrderDate,
deleted.Amount,
deleted.RefNo
INTO
ordersArchive(
OrderID
,OrderDate
,Amount
,RefNo
)
FROM
Orders
AS
o (NOLOCK)
WHERE
o.OrderID <=50000
SELECT
@
rows
= @@ROWCOUNT
SELECT
@cnt = @cnt + 1
SELECT
@msg =
'Lap : '
+
CAST
(@cnt
AS
VARCHAR
) +
' ARCHIVED '
+
cast
(@
rows
AS
VARCHAR
) +
' rows in '
+
cast
(DATEDIFF(
second
, @dt, GETDATE())
as
varchar
) +
' seconds'
RAISERROR(@msg, 0, 1)
WITH
NOWAIT
IF @
rows
= 0 BREAK;
WAITFOR DELAY
'00:00:00.100'
END
|
让我们现在执行这个存储过程，它会需要一些时间，你会在屏幕上看到如下信息。
本文转自UltraSQL51CTO博客，原文链接：http://blog.51cto.com/ultrasql/1733043 ，如需转载请自行联系原作者
