For one reason or another I need to limit the maximum storage a table can consume whilst allowing other tables in the dB to carry on their merry way. Perhaps it is to rein in the DEV team or to prevent a runaway scenario. Okay, a little contrived, but a good thought experiment.
How would I achieve this in SQL 2005?
Move the table in a separate data file and turn off autogrow for that file if you simply want the inserts to fail.
Another possible strategy is adding a trigger instead of insert and ignore new inserts if the table row count exceeds your predetermined value.
answered Oct 29, 2009 at 03:16 PM
Since we're assuming the math has been done to know how many rows fit in the storage limitation, you can use a check constraint on an identity column
If you aren't using an identity column, or the developers were able to modify your identity definition (say, reseed at 0 and have it count by -1), you would want to have the check constraint reference a scalar function that you write that returns the row count of the table and makes sure it is less than the-number-of-rows-you-want.
answered Oct 29, 2009 at 05:44 PM