Can I limit the Size of a Table?


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?

more ▼

asked Oct 29, 2009 at 03:03 PM in Default

Blackhawk-17 gravatar image

11.9k 28 31 37

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?
Oct 29, 2009 at 03:07 PM James Moore
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.
Oct 29, 2009 at 03:33 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Oct 29, 2009 at 03:16 PM

Gianluca Sartori gravatar image

Gianluca Sartori
228 1 4

I won't bother posting an answer since you just covered both of the ideas I had. Nice job.
Oct 29, 2009 at 03:55 PM Tom Staab
That was my though too. Put it on a file & limit that file.
Oct 29, 2009 at 04:07 PM Grant Fritchey ♦♦
trigger is what I thought.
Oct 29, 2009 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, 2009 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, 2009 at 06:11 AM Gianluca Sartori
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 29, 2009 at 05:44 PM

KenJ gravatar image

20.4k 1 4 12

+1... can you come up with a FIFO solution as well?
Oct 30, 2009 at 10:02 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 29, 2009 at 03:03 PM

Seen: 4058 times

Last Updated: Oct 29, 2009 at 04:37 PM