4

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.

Роман Кириллов
  • 139
  • 1
  • 10
  • 1
    See [ROW_NUMBER](https://en.wikipedia.org/wiki/Hypervisor), perhaps - there still SHOULD be a proper defined ordering though. One can try to rely on index order, ymmv; it's much better to pick a business ordering - DateAndTime, perhaps? – user2864740 Sep 30 '17 at 22:38
  • See [here](https://stackoverflow.com/a/4193757/2276098) an example of ROW_NUMBER. – Gabriel Ioniță Sep 30 '17 at 22:47
  • What do you mean by "last". As ordered by what? – Martin Smith Sep 30 '17 at 23:30

1 Answers1

7

Here you go:

DELETE FROM mytable 
WHERE %%physloc%% IN (SELECT TOP 100 %%physloc%% AS RN FROM mytable ORDER BY RN DESC);
Ilyes
  • 14,640
  • 4
  • 29
  • 55