SQL查询删除超过特定行数的最早的行？

Question

我有一个表，其中包含我正在编写的程序的日志条目。我正在寻找关于SQL查询的想法(我正在使用SQL Server Express 2005)，它将保留最新的X个记录，并删除其余的记录。我有一个datetime列，它是日志条目的时间戳。

我认为下面这样的方法是可行的，但是我不确定IN子句在记录数量较大时的性能。性能并不重要，但我可能会在第一次就做到最好。

DELETE FROM MyTable WHERE PrimaryKey NOT IN
(SELECT TOP 10,000 PrimaryKey FROM MyTable ORDER BY TimeStamp DESC)

我应该提到的是，这个查询每天将运行3-4次(作为另一个过程的一部分)，因此与将保留的记录数量相比，每次查询将删除的记录数量将很少。

Answer 1

试试这个：

DECLARE @X int
SELECT @X=COUNT(*) FROM MyTable 
SET @X=@X-10000

DELETE MyTable 
WHERE PrimaryKey IN (SELECT TOP(@x) PrimaryKey 
                     FROM MyTable 
                     ORDER BY TimeStamp ASC
                    ) 

这在某种程度上取决于您删除的行数是否少于10,000行，如果是这样的话，它可能会运行得更快，因为它标识要删除的行，而不是要保留的行。

Answer 2

尝试此操作，使用CTE获取行序列号，然后一次仅删除X个行。您可以更改此变量以适合您的服务器。

添加ReadPast表提示应防止锁定。

DECLARE @numberToDelete INT;
DECLARE @ROWSTOKEEP INT;
SET @ROWSTOKEEP = 50000;
SET @numberToDelete =1000;

WHILE 1=1
BEGIN
    WITH ROWSTODELETE AS
    (
        SELECT ROW_NUMBER() OVER(ORDER BY dtsTimeStamp DESC) rn,
            *
        FROM MyTable 

    )
    DELETE TOP (@numberToDelete) FROM ROWSTODELETE WITH(READPAST)
    WHERE rn>@ROWSTOKEEP;

    IF @@ROWCOUNT=0
        BREAK;
END;

Answer 3

你得到的查询和它得到的一样高效，并且是可读的。

NOT IN和NOT EXISTS比LEFT JOIN/IS NULL更有效，但这只是因为这两列永远不能都为null。你可以使用read this link for a more in-depth comparison。