Ratna Ratna - 10 months ago 42
SQL Question

Is there any pure sql way to delete data from log table in following ways

I have a two tables 1. main_table 2. log_table
On main_table a trigger is defined that copies the affected rows to log_table on delete and update. In log_table the columns are same except two extra column pid(primary key int) and updateat(time stamp).

Every month the cron job is deleting all the records from log_table that are more than 10 days old. which I need to change to delete all records except latest 10 for each row.

I did it via linq in which I am selecting distinct ids, and then I am looping over all the ids and deleting all data for that id except top 10.
This is working except it is painfully slow and unrealistic in production environment. I also tried to move this logic to Stored procedure with cursor but it is still slow.

I am not sure but I think there can other methods for achieving it, where I don't have to loop over?

The server is mssql 2012 if that matters.

Answer Source

Is this what you want?

with todelete as (
      select l.*,
             row_number() over (partition by id order by updatedate desc) as seqnum
      from log_table l
delete todelete
    where seqnum > 10;