|
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?
(comments are locked)
|
|
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. I won't bother posting an answer since you just covered both of the ideas I had. Nice job.
Oct 29 '09 at 03:55 PM
Tom Staab
That was my though too. Put it on a file & limit that file.
Oct 29 '09 at 04:07 PM
Grant Fritchey ♦♦
trigger is what I thought.
Oct 29 '09 at 04:32 PM
Steve Jones - Editor ♦♦
I want to award you the answer but since you opened it up to extra possibilities... can you describe a FIFO solution in more detail?
Oct 30 '09 at 10:03 AM
Blackhawk-17
If you already reached the maximum table size, you could delete from the destination table the number of rows you have in INSERTED and then add the rows. The rows you want to delete are the oldest ones, so maybe something like deleting in join with a TOP(n) subquery should do the trick. If the maximum size is not reached then you could insert without deleting.
Nov 02 '09 at 06:11 AM
Gianluca Sartori
(comments are locked)
|
|
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. +1... can you come up with a FIFO solution as well?
Oct 30 '09 at 10:02 AM
Blackhawk-17
(comments are locked)
|
What do you want to happen with new rows? For the oldest row to be evicted or just to stop inserts after a certain number of rows? Do you want the limit based on data size or row count?
Originally I said storage - so data size - but why not answer for both scenarios as Gianluca has done, size on disk and FIFO after a pre-determined number of rows have populated the table. We'll assume the math has been done to calculate how many rows fit in the storage limitation.