how to set the max no of rows in table

[chezhian_in05]

hi,
I'm using SQL server 2000.i wish to set the max no of rows for a table in database, how can i do it, waiting for your suggestions

With thanks,

R.chezhian

[Igor Soukhov]

hi,
I'm using SQL server 2000.i wish to set the max no of rows for a table in database, how can i do it, waiting for your suggestions

With thanks,

R.chezhian

Why do you need this ?

[ITGURU]

Dear chezhian_in05,

You can achieve this functionality by adding a Insert Trigger on your Table in the Database like below example which i have created on Categories table in my Database:

CREATE TRIGGER [Categories_Insert] ON [dbo].[Categories]
FOR INSERT
AS

DECLARE @RowCount int
set @RowCount = 8 // here u set the maximum number of rows allowed in your table

IF ((SELECT COUNT(*) FROM [dbo].[Categories]) >= @RowCount)
BEGIN
RAISERROR ('Crossed the limit of Records', 16, 1)
END

[Igor Soukhov]

I don't recommend using solution with the trigger - because it would be a REAL performance killer.

If you absolutely need to limit number of records here's the recommended solution:

Create the job in the MS SQL server and schedule it to run every N minutes or hours.
On the each run that job will truncate you table in accordance with you business rules.

[ITGURU]

I am not agree with Igor because in my case table is not locked because we are inserting a new record which does not require a table level lock, which mean does not effect any performance related issue whereas the solution he given is actually using Table Lock because whatever way the record is deleted from table it actually lock the table instead of locking a particular record and another issue is, user will not get any feedback when he insert a new record i.e. record is successfully added into table and silently remove from the table without user knowledge only Admin can know if any message send by Job.

Dear Igor, please do not take it other way, i have just point the problem in your case so that user has full knowledge of Benefit and Drawbacks of both case (mine case u already pointed out) but still ur and mine both solutions are fine and it all depend on user how he want to implement the solution.

[exterminator]

Well, depends on what the database is? If it is MS SQL Server or Sybase, then I would not suggest using the triger solution because the triggers in this case are called once the query has executed. They are all AFTER triggers and hence an overhead in getting everything back to the original state. I would not have any issues with this approach provided the db system supports BEFORE triggers.

Also, throwing an exception/raising an error in such conditions is something I would not suggest.

This logic could either be on the client side or in a stored procedure:
1. Client side - do a select coun(*) on the table and check if the limit has been reached. If not go ahead with the insert else skip it.
2. Stored procedure - the same thing but just that you have all this logic in the stored procedure that accepts all the parameters that are to become a part of the insert statement and returns a value signifying if it went ahead with the insert or not.

But, yeah for me, it seems to me to be a really wierd scenario where one wants to limit the number of rows in a db table. I would wait for the OP to justify that before going any further. Regards.

[Igor Soukhov]

exterminator,

Great explanation, thanks !