Revolutionary Techniques: Setting Up SQL Server Ledger
This video is intended to be a basic guide on how to setup and use the SQL Server Ledger Table feature. The scripts used for the demo are below. Note: This will only work for SQL Server 2019 and above. These scripts are meant for a demonstration and should not be ran on a production instance. #Management
/*Create a database called ConflictMinerals*/
CREATE DATABASE [ConflictMinerals]
/*Create the ConflictMineralsLedgerHistory Table*/
USE ConflictMinerals
CREATE TABLE ConflictMineralsLedger (
Id INT PRIMARY KEY,
MineralName NVARCHAR(100),
SupplierName NVARCHAR(100),
Country NVARCHAR(100),
SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ConflictMineralsLedgerHistory));
/*Insert Data into the Ledger-Enabled Table*/
USE ConflictMinerals
INSERT INTO ConflictMineralsLedger (Id, MineralName, SupplierName, Country)
VALUES
(1, 'Gold', 'Supplier A', 'Country X'),
(2, 'Tin', 'Supplier B', 'Country Y'),
(3, 'Tantalum', 'Supplier C', 'Country Z');
/*Initial Select Statement*/
USE ConflictMinerals
SELECT * FROM ConflictMineralsLedger FOR SYSTEM_TIME ALL;
/*Update ConflictMineralsLedger*/
UPDATE ConflictMineralsLedger
SET SupplierName = 'Supplier D'
WHERE Id = 2;
/*Delete ConflictMineralsLedger*/
DELETE FROM ConflictMineralsLedger
WHERE Id = 3;
/*Select After Changes*/
SELECT * FROM ConflictMineralsLedger FOR SYSTEM_TIME ALL;
#SQLServer#SQLServerLedger#LedgerTable#DataAuditing#Compliance#DataGovernance#DataTransparency#DataSecurity#DatabaseManagement#SQLServerTutorial#DatabaseAuditing#DataChanges#Versioning#TemporalTables#DataAnalysis#DataReporting#AuditingAndCompliance#SQLServerTips#DatabaseDevelopment#DatabaseAdministration#SQLServerFeatures