Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a SQL Server table which I use to store events.

I only want the table to contain the 1000 most recently added rows, when row 1001 is inserted, row 1 should be deleted.

I have both a DateCreatedUTC column and an autonumber column.... I could write a query to execute a delete and trigger it on INSERT but I don't know that this is the most appropriate solution.

share|improve this question
1  
Just a thought, but what's wrong with a view on the original data? Paul... –  Vérace Mar 30 at 14:31
    
Does it have to be 1000 rows exactly, every second of the day? Or can it be a little over 1000 rows and cleaned up periodically? –  Aaron Bertrand Mar 30 at 14:31
    
@AaronBertrand it can be cleaned periodically. –  Matthew Mar 30 at 15:02
    
OK, updated my answer –  Aaron Bertrand Mar 30 at 15:06
3  
You could also consider presizing the table with 1000 rows then just performing updates as a circular buffer –  Martin Smith Mar 30 at 15:16

1 Answer 1

up vote 8 down vote accepted

You can create a view to hide the rest of the rows when they do exist:

CREATE VIEW dbo.Top1000Rows
AS
  SELECT TOP (1000) ...columns...
    FROM dbo.table_name
    ORDER BY DateCreatedUTC DESC;
GO

Then periodically run this in a background job (just play with the frequency until you're happy with how often the table itself is "off", and by how much):

;WITH x AS 
(
  SELECT pk_column, 
    rn = ROW_NUMBER() OVER (ORDER BY DateCreatedUTC DESC)
  FROM dbo.table_name
)
DELETE x WHERE rn > 1000;

If it doesn't have to be exactly 1,000 rows all of the time, then you can potentially forget about the view altogether, knowing that sometimes there will be more than 1,000 rows.

share|improve this answer
    
Good answer, will accept. I guess I was hoping there was some fixed table property to limit the number of rows in this FIFO manner... I guess not! –  Matthew Mar 30 at 15:07
3  
@Matthew No, there is nothing built in for that, seems a peculiar requirement. I am not sure exactly how SQL Server Agent history implements FIFO, but for user data I am not sure it is a common use case. –  Aaron Bertrand Mar 30 at 15:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.