Set Limit for a Table Rows In SQL

Question

I want to set the limit for my table's rows. How can I do it?

For example 50 rows in my table.

Answer 1

Create an AFTER INSERT trigger on the table. Here's something that would be relatively effective with your requirement:

create trigger LimitTable
on YourTableToLimit
after insert
as
    declare @tableCount int
    select @tableCount = Count(*)
    from YourTableToLimit

    if @tableCount > 50
    begin
        rollback
    end
go

Answer 2

Use a CHECK constraint. E.g.:

CREATE TABLE t1 (x TINYINT NOT NULL UNIQUE CHECK (x BETWEEN 1 AND 50));

Answer 3

Are you referring to limiting the results of a query?

If so, with SQL Server 2008 you can use TOP

SELECT TOP 50 *
FROM Table

If you're looking at actually limiting the amount of records in the database table, then an IF statement in a TRIGGER, like @Shark has posted, would be my solution.

Answer 4

What you want is having a INSTEAD OF INSERT trigger that checks the # of current rows. If already 50, you will raise an error by using RAISERROR. If not, you just insert the record.

Warning! Untested code ahead. It might contain typos or slight syntax errors. The code is supposed to show you the concepts involved. Tweak and adjust to your needs accordingly.

Like this:

CREATE TRIGGER checktablelimit 
ON yourtable 
INSTEAD OF INSERT 
AS 
  DECLARE @currentCount INT 

  SELECT @currentCount = COUNT(*) 
  FROM   yourtabletolimit 

  IF @currentCount = 50 
    BEGIN 
        RAISERROR ('Table already has 50 records', 
                   11, 
                   1); 
    END 
  ELSE 
    BEGIN 
        INSERT INTO yourtable 
                    (field1, 
                     field2, 
                     field3) 
        SELECT field1, 
               field2, 
               field3 
        FROM   inserted 
    END 

GO