SQL Server 2000: Limit number of rows

views:

337

answers:

+2 Q:

SQL Server 2000: Limit number of rows

Hello sql gurus,

Is there a way to limit the number of rows in a SQL Server 2000 database, such that older rows are deleted when new ones come in?

I have some data that I'd like to keep around for a about thirty days - after that, I don't care whether the data lays around or is deleted - as long as the table doesn't become huge.

Any other suggestions or welcome - I'm decent with programming against a database, but I'm by no means a DBA.

Thanks.

+3 A:

The typical way to do this would be to create a stored proc or a chunk of T-SQL code that deletes the old entries, and have this chunk of T-SQL run every night or every week or whatever you need.

There's no system-built-in way to limit the number of rows and have SQL Server toss out the oldest ones automatically.

MArc

marc_s 2009-06-26 16:17:52

Hi marc_s, thanks for the suggestion. That sounds like a good idea. How would *you* schedule the query? Is that what a "job" is for? Like I said, I'm pretty ignorant of most DB concepts other than simple sql. Thanks again.

Charles 2009-06-26 20:33:54

@Charles: You can create a job in SQL Server Agent, which you can then schedule. This is what he (and I) were referring to about scheduling the query.

Eric 2009-06-26 21:11:54

Excellent - I'll look into scheduled jobs. Thanks for the suggestions.

Charles 2009-06-26 22:52:00

+5 A:

Not explicitly, no. However, you can set up a TRIGGER on the INSERT for the database to delete any rows older than thirty days (so you only delete rows as you add more, not just for the sake of it). Or, you can use SQL Server Agent and set up a Stored Procedure to do a

delete from table where insertdate <= dateadd(d, -30, getdate())

Eric 2009-06-26 16:18:24

Do you know if the trigger would run synchronously with the insert? In other words, would the trigger potentially cause the insert to take longer? So far, using a trigger sounds like the best bet, but I wonder how deleting x amount of rows will affect the duration of future queries. Thanks for the suggestion.

Charles 2009-06-26 20:30:24

The answer to that is a big "It depends." The trigger runs once (and only once) per insert, no matter how large the insert (you could do 1 million rows, and the trigger only runs once). Also, the trigger runs after the insert, not during. If you have a nonclustered index on your date field, the delete should happen fairly quickly, regardless of how many rows you have. If you don't have an index on the field, and aren't taking advantage of bulk inserts, then you could hit some performance issues w/ your database.

Eric 2009-06-26 20:39:36

Thanks - that's a very detailed response. That should work well for my purposes.

Charles 2009-06-26 21:06:43

ansaurus

tags:

views:

answers:

SQL Server 2000: Limit number of rows

related questions