Keep the last 'x' results in a database

Question

I would like to ask if there is a quick way to keep the last 'x' inserted rows in a database.

For example, i have an application through which users can search for items and I want to keep the last 10 searches that each user has made. I do not want to keep all his searches into the database as this will increase db_size. Is there a quick way of keeping only the latest 10 searches on my db or shall i check every time:

A) how many searches has been stored on database so far
B) if (searches = 10) delete last row
C) insert new row

I think that this way will have an impact on performance as it will need 3 different accesses on the database: check, delete and insert

Answer 1

I don't think an easy/quick way to do this. Based on your conditions i created the below stored procedure.

I considered SearchContent which is going to store the data.

CREATE TABLE SearchContent (
    Id          INT IDENTITY (1, 1),
    UserId      VARCHAR (8),
    SearchedOn  DATETIME,
    Keyword     VARCHAR (40)
)

In the stored procedure passing the UserId, Keyword and do the calculation. The procedure will be,

CREATE PROCEDURE [dbo].[pub_SaveSearchDetails]
(
    @UserId     VARCHAR (8),
    @Keyword    VARCHAR (40)
)
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @Count  AS INT = 0;
    -- A) how many searches has been stored on database so far 
    SELECT @Count = COUNT(Keyword) FROM SearchContent WHERE UserId = @UserId

    IF @Count >= 10
    BEGIN
        -- B) if (searches = 10) delete last row 
        DELETE FROM SearchContent WHERE Id IN ( SELECT TOP 1 Id FROM SearchContent WHERE UserId = @UserId ORDER BY SearchedOn ASC)
    END

    -- C) insert new row
    INSERT INTO SearchContent (UserId, SearchedOn, Keyword) 
    VALUES (@UserId, GETDATE(), @Keyword)
END

Sample execution: EXEC pub_SaveSearchDetails 'A001', 'angularjs'