How should I limit a table to N rows, discarding oldest? 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.
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 &gt; 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.