Set limit for table rows in SQL

Question

I want to set a limit for table rows. How can i do this?

For example, 50 rows in my table.

Answer 1

Create an AFTER INSERT trigger in the table. Here is something that will 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 the CHECK constraint. For instance:.

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

Answer 3

Do you mean limiting the results of a query?

If so, in SQL Server 2008 you can use TOP

 SELECT TOP 50 * FROM Table 

If you look at the actual limit on the number of records in the database table, then the IF message is displayed in TRIGGER , for example @Shark , would be my solution.

Answer 4

You want to have an INSTEAD OF INSERT trigger that checks the number of current rows. If it is already 50, you will RAISERROR error using RAISERROR . If not, you just insert write.

A warning! Unconfirmed code ahead. It may contain typos or minor syntax errors. The code is supposed to show you the concepts used. Adjust and adjust 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