Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://learn.microsoft.com/en-us/azure/azure-sql-edge/data-retention-enable-disable below:

Enable and disable data retention policies - Azure SQL Edge

Important

Azure SQL Edge will be retired on September 30, 2025. For more information and migration options, see the Retirement notice.

Note

Azure SQL Edge no longer supports the ARM64 platform.

This article describes how to enable and disable data retention policies for a database and a table.

The following example shows how to enable data retention by using ALTER DATABASE.

ALTER DATABASE [<DatabaseName>] SET DATA_RETENTION ON;

The following command can be used to check if data retention is enabled for a database.

SELECT is_data_retention_enabled,
    name
FROM sys.databases;

Data Retention must be enabled for each table for which you want data to be automatically purged. When data retention is enabled on the database and the table, a background system task periodically scans the table to identify and delete any obsolete (aged) rows. Data retention can be enabled on a table either during table creation using CREATE TABLE or by using ALTER TABLE.

The following example shows how to enable data retention for a table by using CREATE TABLE.

CREATE TABLE [dbo].[data_retention_table] (
    [dbdatetime2] DATETIME2(7),
    [product_code] INT,
    [value] CHAR(10),
    CONSTRAINT [pk_current_data_retention_table] PRIMARY KEY CLUSTERED ([product_code])
)
WITH (
        DATA_DELETION = ON (
            FILTER_COLUMN = [dbdatetime2],
            RETENTION_PERIOD = 1 day
            )
        );

The WITH (DATA_DELETION = ON (FILTER_COLUMN = [dbdatetime2], RETENTION_PERIOD = 1 day)) part of the CREATE TABLE command sets the data retention on the table. The command uses the following required parameters:

• DATA_DELETION: Indicates whether data retention is ON or OFF.

• FILTER_COLUMN: Name on the column in the table, which will be used to ascertain if the rows are obsolete or not. The filter column can only be a column with the following data types:

  • date
  • smalldatetime
  • datetime
  • datetime2
  • datetimeoffset

• RETENTION_PERIOD: An integer value followed by a unit descriptor. The allowed units are DAY, DAYS, WEEK, WEEKS, MONTH, MONTHS, YEAR and YEARS.

The following example shows how to enable data retention for table by using ALTER TABLE.

ALTER TABLE [dbo].[data_retention_table]
SET (
    DATA_DELETION = ON (
        FILTER_COLUMN = [timestamp],
        RETENTION_PERIOD = 1 day
    )
)

The following command can be used to check the tables for which data retention is enabled

SELECT name,
    data_retention_period,
    data_retention_period_unit
FROM sys.tables;

A value of data_retention_period = -1 and data_retention_period_unit as INFINITE, indicates that data retention isn't set on the table.

The following query can be used to identify the column used as the filter_column for data retention.

SELECT name
FROM sys.columns
WHERE is_data_deletion_filter_column = 1
    AND object_id = object_id(N'dbo.data_retention_table', N'U');

The data retention setting on the database and the table are used in conjunction to determine if autocleanup for aged rows runs on the tables.

Data retention can be disabled on a table by using ALTER TABLE. The following command can be used to disable data retention on a table.

ALTER TABLE [dbo].[data_retention_table]
SET (DATA_DELETION = OFF);

Data retention can be disabled on a table by using ALTER DATABASE. The following command can be used to disable data retention on a database.

ALTER DATABASE [<DatabaseName>] SET DATA_RETENTION OFF;

• Data Retention and Automatic Data Purging
• Manage historical data with retention policy

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4