Trying to create a rolling row limit stored procedure-CodePudding

Platform: SQL Server

Goal: get the current number of rows and subtract from it the amount I want to keep and then delete the remainder. I was planning to turn it into a stored procedure if I could get it working.

For the code I was thinking something like this:

SET N = (EXEC sp_spaceused dbo.Name rows)
SET  D = (%N%-30000000)

DELETE TOP (%D%) FROM dbo.Name

I used sp_spaceused to avoid locking the table to input statements, as would be the case with count.

CodePudding user response：

You can use an updatable CTE or derived table to delete. Assuming you wanted to delete random rows from the table, you don't need any further calculations.

Don't use sp_spaceused, get the data from sys.partitions instead

DECLARE @N int = (
  SELECT SUM(p.rows)
  FROM sys.partitions p
  INNER JOIN sys.tables t ON p.[object_id] = t.[object_id]
  INNER JOIN sys.schemas s ON s.[schema_id] = t.[schema_id]
  WHERE t.name = N'Name'
    AND s.name = N'dbo'
    AND p.index_id IN (0,1)
);

WITH cte AS (
    SELECT TOP (@N - 30000000)
      *
    FROM dbo.Name
)
DELETE FROM cte;

db<>fiddle