How to delete last N records in SQL Server 2008 R2 database?

Question

I have a SQL Server 2008 R2 Express database with one table with rows. And when database size became about 10gb, I need to clean the N last records from this table.

I need something like this, but for SQL Server

DELETE FROM mytable 

WHERE ROWID IN (SELECT ROWID FROM mytable ORDER BY ROWID ASC LIMIT 100)

Thanks.

Database structure:

strSQL = "SELECT DateAndTime

                ,TagName

                ,Val

                ,SetPoint

                ,Limit_H

                ,Limit_L

                ,Result 

          FROM dbo.Statistic...."

UPD. It is not depend on "DateAndTime", becouse i have to free space, but 1 day (or 1 week) may consist only 1 record, as i understand...so i need delete 200K records.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

Here you go:

DELETE FROM mytable 
WHERE %%physloc%% IN (SELECT TOP 100 %%physloc%% AS RN FROM mytable ORDER BY RN DESC);

How to delete last N records in SQL Server 2008 R2 database?

Sign up