Delete record from table over 5000 count.

Hi, I want to delete from table when the record count is over 5000. That is, the table's record cannot over 5000 count. How? row_number() seem to be used...

I found it myself the Help document.

The solution is ..

WITH OrderedOrders AS
(SELECT ROW_NUMBER() OVER (order by date desc)as RowNumber FROM EventInfo )
DELETE FROM OrderedOrders
WHERE RowNumber > 5000;

Sometimes the execution of this query is somewhat slow.
But I don't know why it is.

|||

Sometimes the execution of this query is somewhat slow.

Maybe, you don't have an index on the "date" column?

|||

Right! The table doesn't have any key column.

|||Please, mark helpful answers as "Answer".|||

remedios_ wrote:

Right! The table doesn't have any key column.

A column doesn't have to be a "key" column to have an index built on it.|||

If I should make index for this table, the DATETIME type column will be a member of the index.

This table is for saving the event messages which may occur in same time.
As a result, this column data may be not unique.
Any other column of this table is not unique also...