SEEDER QUESTION:
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.
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
alter table <your-table>
add constraint <your-constraint>
check (your-identity-column < the-number-of-rows-you-want)
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.
