Table is growing too big

I have a table that receives data every second.  What I need to do is limit the size of the table so it stops growing ( I cannot change the time for input ). We do not need to keep this many records and I have decided on the time frame to keep. What I would like to do is create a trigger that after INSERT, DELETES the oldest row, I assume this will keep the table at the size I want. Any suggestions for code? Will the INSERT and DELETE trigger slow down the DB because its every second? Or is there another way to accomplish this with as little impact as possible. I just need a starting point!
dennisjameshowardAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
A trigger would be too much overhead.

First, cluster the table by the datetime of insert.

Then you can have a job that runs every <whatever> minutes that deletes the oldest <whatever> minutes of data.

That should reduce the impact as much as reasonably possible.
0
 
chapmandewCommented:
A good way to do this is to use partitioning in 2005 to easily swap out old records...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.