如何在一段时间后自动删除SQL Server中的行（例如：10分钟） [英] How to delete a row in SQL server automatically after some time(ex: 10 mins)

问题描述

我有很多记录的数据库。当我插入一个记录时，它需要花费当前时间，比如10分钟，记录应该被删除。我们可以做任何帮助frds



我尝试了什么：

i have database with many records.when i am inserting a record it will take the current time after some time like 10 mins the record should be deleted.how we can do any help frds

What I have tried:

i have database with many recordswhen i am inserting a record it will take the current time after some time like 10 mins the record should be deleted.how we can do any help frds

推荐答案

创建工作是一种可能性，例如参见安排工作 [ ^ ]和创建Transact-SQL作业步骤 [ ^ ]。但是，请记住，这种方法不适用于Express Editions，因为SQL Agent已被禁用。



但是要提供替代方案，你确定你想永久删除记录？更常见的方法是控制记录的有效性。换句话说，如果它超过10分钟，请不要获取它，但让它仍然驻留在数据库中。实现此目的的一种简单方法是，您有一个日期时间字段，该字段在有效性字段中具有默认的当前日期时间，并且在您的提取中，您可以消除超过所需时间的行。

Creating a job is one possibility, for examples see Schedule a Job[^] and Create a Transact-SQL Job Step[^] . However, keep in mind that this approach won't work with Express Editions since the SQL Agent is disabled for them.

But to give an alternative, are you sure you want to permanently delete the records? A much more common approach is to control the validity of a record. In other words if it's older than the 10 minutes, don't fetch it but let it still reside in the database. An easy way to achieve this is that you have a datetime field which has current datetime as default in a validity field and in your fetch you eliminate rows that are older than the desired amount of time.

Consider the following:

CREATE TABLE ValidityTest (
   col1 varchar(100),
   Created datetime DEFAULT GETDATE()
);

INSERT INTO ValidityTest (col1) VALUES ('Some row');

SELECT *
FROM ValidityTest 
WHERE Created >= DATEADD(minute, -10, GETDATE())

如果您愿意，可以制作通过创建查询视图，查询更容易，例如

if you want, you can make the query easier by creating a view to query from, for example

CREATE VIEW ValidRows AS
SELECT *
FROM ValidityTest 
WHERE Created >= DATEADD(minute, -10, GETDATE());

现在您可以轻松查询有效数据

Now you can easily query valid data

SELECT * FROM ValidRows;

如果您愿意，可以每隔一段时间运行一次清理删除，删除早于欲望金额的记录时间（例如几个月），但只要s速度或速度不是问题，这不是必需的。

If you like, you can run a cleanup delete every once in a while to remove records older than desiread amount of time (months for example) but as long as the space or speed isn't an issue, this isn't necessary.

这篇关于如何在一段时间后自动删除SQL Server中的行（例如：10分钟）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！