How to limit rows in the table ? How to insert row on the top? SQL Server

Question

Firstly - I want to limit my inserted rows into the database. I.e. I want to have max of 100 rows in my table (i don't want to have version count(*), if >100, delete...)

Secondary - I would like something like pushing (on insert) into the table. I.e:

ID |  Name    |      City
 0 |   Mike   |    New York


ID |  Name    |      City
1  |   David  |      Pekin   -> as insert on the top!
0  |   Mike   |    New York


ID |  Name    |      City
2  |   Marcus |     Warsaw  -> as next
1  |   David  |      Pekin   
0  |   Mike   |    New York

Best regards David

Answer 1

Both of your conditions can be done for a 100 row table. Or, even a 1,000 row table. But, the example below is not recommended actions for a production table.

RE: "have max of 100 rows" Create table, then add an INSTEAD OF INSERT trigger that checks size of table before allowing an insert.

RE: "something like pushing (on insert) into the table." Create table with a clustered primary key (recommended) but with a DESC key (not recommended for anything but small, seldom used tables). CLUSTERED index is physically ordered. Therefore, adding a next larger number (as in an IDENTITY sequence) will push the new entry on to the top of the index. SELECT will return rows in disk order. For the purposes of a 100 row table with a clustered index, disk order and index order are identical.

Code:
NOTE: code below limits max_size to 3 for demonstration. Set @max_size = 100 for your example case

-->-- create table with descending order for primary key.  will act like a push down stack
IF OBJECT_ID('dbo.so_limit_size') IS NOT NULL DROP TABLE dbo.so_limit_size
CREATE TABLE dbo.so_limit_size ( 
    id TINYINT IDENTITY(1,1)
    , name VARCHAR(100) 
    , city VARCHAR(100)
    , CONSTRAINT pk_so_limit_size PRIMARY KEY CLUSTERED (id DESC) -- DESC makes it work like a push down stack
    )

INSERT INTO dbo.so_limit_size (name, city)
VALUES
( 'Mike', 'New York City' )
, ( 'David', 'Pekin' )
, ( 'Marcus', 'Warsaw' )


SELECT * FROM dbo.so_limit_size  -- 3 rows

GO
CREATE TRIGGER dbo.limit_size ON dbo.so_limit_size
INSTEAD OF INSERT AS
    SET NOCOUNT ON
    -- purpose:  limit size of table to @max_size.  insert batch of 1 or more that exceeds @max_size will not be allowed

    DECLARE @max_size TINYINT = 3  -- size limit of table

    DECLARE @existing_count TINYINT = (SELECT COUNT(*) FROM dbo.so_limit_size)
        , @insert_count TINYINT = (SELECT COUNT(*) FROM Inserted )
    PRINT 'existing_count = ' + LOWER(@existing_count) + ' new insert count = ' + LOWER(@insert_count)

    IF @existing_count + @insert_count >= 3
    BEGIN
        PRINT 'insert will cause table count to exceed max_size.  insert aborted.  max_size = ' + LOWER(@max_size) 
    END     
    ELSE
    BEGIN
        PRINT 'table count less than max_size.  insert allowed.  max_size = ' + LOWER(@max_size)  --<<-- demonstration, print is not a recommended practice for a trigger
        INSERT INTO dbo.so_limit_size (name, city)
        SELECT name, city FROM inserted
    END
GO
INSERT INTO dbo.so_limit_size (name, city)
VALUES
( 'Zorba', 'Athens' ) -- will not be allowed if @max_size = 3


SELECT * FROM dbo.so_limit_size

Answer 2

@Hans Kesting noted that his interpretation of the 100 row limit "should push out the oldest one(s)". That is a valid interpretation. So, I created an answer that does that. The following is identical to my previous answer except that the trigger now deletes the oldest rows before the insert. Table max size is maintained. Please see my other answer for explanations of clustered index with descending key and instead of trigger.

CODE:

-->-- create table with descending order for primary key.  will act like a push down stack
IF OBJECT_ID('dbo.so_limit_size') IS NOT NULL DROP TABLE dbo.so_limit_size
CREATE TABLE dbo.so_limit_size ( 
    id TINYINT IDENTITY(1,1)
    , name VARCHAR(100) 
    , city VARCHAR(100)
    , CONSTRAINT pk_so_limit_size PRIMARY KEY CLUSTERED (id DESC) -- DESC makes it work like a push down stack
    )

INSERT INTO dbo.so_limit_size (name, city)
VALUES
( 'Mike', 'New York City' )
, ( 'David', 'Pekin' )
, ( 'Marcus', 'Warsaw' )


SELECT * FROM dbo.so_limit_size  -- 3 rows

GO
CREATE TRIGGER dbo.limit_size ON dbo.so_limit_size
INSTEAD OF INSERT AS
    SET NOCOUNT ON
    -- purpose:  limit size of table to @max_size.  insert batch of 1 or more that exceeds @max_size will not be allowed

    DECLARE @max_size TINYINT = 3  -- size limit of table

    DECLARE @existing_count TINYINT = (SELECT COUNT(*) FROM dbo.so_limit_size)
        , @insert_count TINYINT = (SELECT COUNT(*) FROM Inserted )
    PRINT 'existing_count = ' + LOWER(@existing_count) + ' new insert count = ' + LOWER(@insert_count)

    IF @existing_count + @insert_count >= 3
    BEGIN
        -- for FIFO stack, delete oldest rows before insert if @max_size exceeded
        PRINT 'insert will cause table count to exceed max_size.  oldest row(s) deleted before insert.  max_size = ' + LOWER(@max_size) 
        DELETE FROM dbo.so_limit_size
        WHERE Id IN ( SELECT TOP (@insert_count) Id FROM dbo.so_limit_size ORDER BY Id ASC )
        
        INSERT INTO dbo.so_limit_size (name, city)
        SELECT name, city FROM inserted

    END     
    ELSE
    BEGIN
        PRINT 'table count less than max_size.  insert allowed.  max_size = ' + LOWER(@max_size)  --<<-- demonstration, print is not a recommended practice for a trigger
        INSERT INTO dbo.so_limit_size (name, city)
        SELECT name, city FROM inserted
    END
GO
INSERT INTO dbo.so_limit_size (name, city)
VALUES
( 'Zorba', 'Athens' ) -- will not be allowed if @max_size = 3
, ('Sriram', 'Chennai')


SELECT * FROM dbo.so_limit_size