Share via

Mengkueri data dalam tabel temporal versi sistem

  • Artikel

Berlaku untuk: SQL Server 2016 (13.x) dan database Azure SQL yang lebih baru Azure SQL Managed Instance

Saat Anda ingin mendapatkan status data terbaru (saat ini) dalam tabel temporal, Anda bisa mengkueri dengan cara yang sama seperti Anda mengkueri tabel non-temporal. PERIOD Jika kolom tidak disembunyikan, nilainya muncul dalam SELECT * kueri. Jika Anda menentukan PERIOD kolom sebagai HIDDEN, nilainya tidak muncul dalam SELECT * kueri. PERIOD Saat kolom disembunyikan, Anda harus mereferensikan PERIOD kolom secara khusus dalam SELECT klausa untuk mengembalikan nilai untuk kolom ini.

Untuk melakukan semua jenis analisis berbasis waktu, gunakan klausa baru FOR SYSTEM_TIME dengan empat subklausus khusus temporal untuk mengkueri data di seluruh tabel saat ini dan riwayat. Untuk informasi selengkapnya tentang klausul ini, lihat Tabel Temporal dan FROM (Transact-SQL)

  • AS OF <date_time>
  • FROM <start_date_time> TO <end_date_time>
  • BETWEEN <start_date_time> AND <end_date_time>
  • CONTAINED IN (<start_date_time>, <end_date_time>)
  • ALL

FOR SYSTEM_TIME dapat ditentukan secara independen untuk setiap tabel dalam kueri. Ini dapat digunakan di dalam ekspresi tabel umum, fungsi bernilai tabel, dan prosedur tersimpan. Saat menggunakan alias tabel dengan tabel temporal, FOR SYSTEM_TIME klausa harus disertakan antara nama tabel temporal dan alias (lihat Kueri untuk waktu tertentu menggunakan AS OF contoh kedua subklaus ).

Kueri untuk waktu tertentu menggunakan AS OF subklaus

AS OF Gunakan subklaus ketika Anda perlu mengonstruksi ulang status data seperti pada waktu tertentu di masa lalu. Anda dapat menyusun ulang data dengan presisi jenis datetime2 yang ditentukan dalam PERIOD definisi kolom.

AS OF Subklaus dapat digunakan dengan literal konstan atau dengan variabel, sehingga Anda dapat menentukan kondisi waktu secara dinamis. Nilai yang disediakan ditafsirkan sebagai waktu UTC.

Contoh pertama ini mengembalikan status dbo. Tabel departemen AS OF tanggal tertentu di masa lalu.

/*State of entire table AS OF specific date in the past*/
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME AS OF '2021-09-01 T10:00:00.7230011';

Contoh kedua ini membandingkan nilai antara dua titik waktu untuk subset baris.

DECLARE @ADayAgo DATETIME2;
SET @ADayAgo = DATEADD(day, -1, sysutcdatetime());

/*Comparison between two points in time for subset of rows*/
SELECT D_1_Ago.[DeptID],
    D.[DeptID],
    D_1_Ago.[DeptName],
    D.[DeptName],
    D_1_Ago.[ValidFrom],
    D.[ValidFrom],
    D_1_Ago.[ValidTo],
    D.[ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME AS OF @ADayAgo AS D_1_Ago
INNER JOIN [Department] AS D
    ON D_1_Ago.[DeptID] = [D].[DeptID]
        AND D_1_Ago.[DeptID] BETWEEN 1 AND 5;

Menggunakan tampilan dengan AS OF subklaus dalam kueri temporal

Menggunakan tampilan berguna dalam skenario ketika analisis titik waktu yang kompleks diperlukan. Contoh umumnya adalah membuat laporan bisnis hari ini dengan nilai untuk bulan sebelumnya.

Biasanya, pelanggan memiliki model database yang dinormalisasi, yang melibatkan banyak tabel dengan hubungan kunci asing. Mencari tahu bagaimana data dari model yang dinormalisasi melihat titik di masa lalu bisa menjadi tantangan, karena semua tabel berubah secara independen pada irama mereka sendiri.

Dalam hal ini, opsi terbaik adalah membuat tampilan dan menerapkan AS OF subklausus ke seluruh tampilan. Menggunakan pendekatan ini memungkinkan Anda untuk memisahkan pemodelan lapisan akses data dari analisis titik waktu, karena SQL Server menerapkan klausul AS OF secara transparan ke semua tabel temporal yang berpartisipasi dalam definisi tampilan. Selain itu, Anda dapat menggabungkan temporal dengan tabel non-temporal dalam tampilan yang sama dan AS OF hanya diterapkan pada tabel temporal. Jika tampilan tidak mereferensikan setidaknya satu tabel temporal, menerapkan klausa kueri temporal ke dalamnya gagal dengan kesalahan.

Kode sampel berikut membuat tampilan yang menggabungkan tiga tabel temporal: Department, , CompanyLocationdan LocationDepartments:

CREATE VIEW [dbo].[vw_GetOrgChart]
AS
SELECT [CompanyLocation].LocID,
    [CompanyLocation].LocName,
    [CompanyLocation].City,
    [Department].DeptID,
    [Department].DeptName
FROM [dbo].[CompanyLocation]
LEFT JOIN [dbo].[LocationDepartments]
    ON [CompanyLocation].LocID = LocationDepartments.LocID
LEFT JOIN [dbo].[Department]
    ON LocationDepartments.DeptID = [Department].DeptID;
GO

Sekarang Anda bisa mengkueri tampilan menggunakan AS OF subklaus dan harfiah datetime2 :

/* Querying view AS OF */
SELECT * FROM [vw_GetOrgChart]
FOR SYSTEM_TIME AS OF'2021-09-01 T10:00:00.7230011';

Atau, Anda dapat mengkueri tampilan menggunakan AS OF subklausim dengan zona waktu lokal dan AT TIME ZONE:

/* Querying view AS OF with local time*/
DECLARE @LocalTime DATETIMEOFFSET = '2021-09-01 10:00:00.7230011 -07:00';

SELECT * FROM [vw_GetOrgChart]
FOR SYSTEM_TIME AS OF @LocalTime AT TIME ZONE 'UTC';

Kueri untuk perubahan pada baris tertentu dari waktu ke waktu

Subklausus temporal , BETWEEN ... AND dan CONTAINED IN berguna ketika Anda perlu mendapatkan semua perubahan historis FROM ... TOuntuk baris tertentu dalam tabel saat ini (juga dikenal sebagai audit data).

Dua subklauster pertama mengembalikan versi baris yang tumpang tindih dengan periode tertentu (yaitu, yang dimulai sebelum periode tertentu dan berakhir setelahnya), sementara CONTAINED IN hanya mengembalikan yang ada dalam batas periode yang ditentukan.

Jika Anda mencari versi baris non-saat ini saja, Anda harus mengkueri tabel riwayat secara langsung, untuk performa kueri terbaik. Gunakan ALL saat Anda perlu mengkueri data saat ini dan historis tanpa batasan apa pun.

/* Query using BETWEEN...AND sub-clause*/
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo],
    IIF(YEAR(ValidTo) = 9999, 1, 0) AS IsActual
FROM [dbo].[Department]
FOR SYSTEM_TIME BETWEEN '2021-01-01' AND '2021-12-31'
WHERE DeptId = 1
ORDER BY ValidFrom DESC;

/* Query using CONTAINED IN sub-clause */
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME CONTAINED IN ('2021-04-01', '2021-09-25')
WHERE DeptId = 1
ORDER BY ValidFrom DESC;

/* Query using ALL sub-clause */
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo],
    IIF(YEAR(ValidTo) = 9999, 1, 0) AS IsActual
FROM [dbo].[Department]
FOR SYSTEM_TIME ALL
ORDER BY [DeptID],
    [ValidFrom] DESC;

Langkah berikutnya