ASP.NET Hosting Tutorial: Knowing SQL System-Versioned Tables

One aspect of SQL that enables automatic tracking of data changes over time is system-versioned tables. In order to facilitate auditing changes and retrieving historical data, these tables aid in maintaining history versions of records.

How Do Tables With System Versions Operate?
The components of a system-versioned table are

Current data is stored in the main table.
History table: Maintains records from the past automatically.

Making a Table with System Versions
The SQL script that follows generates a history table and a system-versioned table called Employees.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Position NVARCHAR(100),
    Salary DECIMAL(10, 2),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.EmployeesHistory
    )
);

Querying System-Versioned Tables

Get Current Data

SELECT *
FROM Employees;

Get Historical Data

SELECT *
FROM EmployeesHistory;

Retrieve Data at a Specific Time

SELECT *
FROM Employees
FOR SYSTEM_TIME AS OF '2024-03-01T10:00:00';

Advantages of System-Versioned Tables

• Automatically tracks data changes.
• Provides historical auditing without manual tracking.
• Allows time-travel queries to retrieve past data states.
• Ensures data integrity and compliance with regulatory requirements.

System-versioned tables are highly beneficial for applications that require auditing, data recovery, and change tracking. 

Best ASP.NET Core 9 Hosting Recommendation

One of the most important things when choosing a good ASP.NET Core 9 hosting is the feature and reliability. HostForLIFE is the leading provider of Windows hosting and affordable ASP.NET Core, their servers are optimized for PHP web applications. The performance and the uptime of the hosting service are excellent and the features of the web hosting plan are even greater than what many hosting providers ask you to pay for. 

At HostForLIFEASP.NET, customers can also experience fast ASP.NET Core hosting. The company invested a lot of money to ensure the best and fastest performance of the datacenters, servers, network and other facilities. Its datacenters are equipped with the top equipments like cooling system, fire detection, high speed Internet connection, and so on. That is why HostForLIFEASP.NET guarantees 99.9% uptime for ASP.NET Core. And the engineers do regular maintenance and monitoring works to assure its Orchard hosting are security and always up.