Роман Кириллов Роман Кириллов - 3 years ago 249
SQL Question

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

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.

Answer Source

Here you go:

DELETE FROM mytable 
WHERE %%physloc%% IN (SELECT TOP 100 %%physloc%% AS RN FROM mytable ORDER BY RN DESC);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download