Tabel temporal

  • Artikel

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

Tabel temporal (juga dikenal sebagai tabel temporal versi sistem) adalah fitur database yang membawa dukungan bawaan untuk memberikan informasi tentang data yang disimpan dalam tabel kapan saja, bukan hanya data yang benar pada saat ini.

Mulai menggunakan tabel temporal versi sistem, dan tinjau skenario penggunaan tabel Temporal.

Apa itu tabel temporal versi sistem?

Tabel temporal versi sistem adalah jenis tabel pengguna yang dirancang untuk menyimpan riwayat lengkap perubahan data, memungkinkan analisis titik waktu yang mudah. Jenis tabel temporal ini disebut sebagai tabel temporal versi sistem karena periode validitas untuk setiap baris dikelola oleh sistem (yaitu, mesin database).

Setiap tabel temporal memiliki dua kolom yang ditentukan secara eksplisit, masing-masing dengan jenis data datetime2 . Kolom ini disebut sebagai kolom periode. Kolom periode ini digunakan secara eksklusif oleh sistem untuk merekam periode validitas untuk setiap baris, setiap kali baris dimodifikasi. Tabel utama yang menyimpan data saat ini disebut sebagai tabel saat ini, atau hanya sebagai tabel temporal.

Selain kolom periode ini, tabel temporal juga berisi referensi ke tabel lain dengan skema cermin, yang disebut tabel riwayat. Sistem menggunakan tabel riwayat untuk menyimpan versi baris sebelumnya secara otomatis setiap kali baris dalam tabel temporal diperbarui atau dihapus. Selama pembuatan tabel temporal, pengguna dapat menentukan tabel riwayat yang ada (yang harus sesuai dengan skema) atau membiarkan sistem membuat tabel riwayat default.

Mengapa temporal?

Sumber data nyata bersifat dinamis dan lebih sering daripada keputusan bisnis tidak bergantung pada wawasan yang dapat didapat analis dari evolusi data. Kasus penggunaan untuk tabel temporal meliputi:

  • Mengaudit semua perubahan data dan melakukan forensik data jika diperlukan
  • Mengkonstruksi ulang status data kapan saja di masa lalu
  • Menghitung tren dari waktu ke waktu
  • Mempertahankan dimensi yang berubah secara perlahan untuk aplikasi dukungan keputusan
  • Memulihkan dari perubahan data yang tidak disengaja dan kesalahan aplikasi

Bagaimana cara kerja temporal?

Penerapan versi sistem untuk tabel diimplementasikan sebagai sepasang tabel, tabel saat ini, dan tabel riwayat. Dalam masing-masing tabel ini, dua kolom datetime2 tambahan digunakan untuk menentukan periode validitas untuk setiap baris:

  • Kolom periode mulai: Sistem merekam waktu mulai untuk baris di kolom ini, biasanya ditandai sebagai ValidFrom kolom.
  • Kolom akhir periode: Sistem merekam waktu akhir untuk baris di kolom ini, biasanya ditandai sebagai ValidTo kolom.

Tabel saat ini berisi nilai saat ini untuk setiap baris. Tabel riwayat berisi setiap nilai sebelumnya ( versi lama) untuk setiap baris, jika ada, dan waktu mulai dan waktu akhir untuk periode yang valid.

Diagram showing how a temporal table works.

Skrip berikut mengilustrasikan skenario dengan informasi karyawan:

CREATE TABLE dbo.Employee (
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR(100) NOT NULL,
    [Position] VARCHAR(100) NOT NULL,
    [Department] VARCHAR(100) NOT NULL,
    [Address] NVARCHAR(1024) NOT NULL,
    [AnnualSalary] DECIMAL(10, 2) NOT NULL,
    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Untuk informasi selengkapnya, lihat Membuat tabel temporal versi sistem.

  • Sisipan: Sistem mengatur nilai untuk ValidFrom kolom ke waktu mulai transaksi saat ini (di zona waktu UTC) berdasarkan jam sistem dan menetapkan nilai untuk ValidTo kolom ke nilai 9999-12-31maksimum . Ini menandai baris sebagai terbuka.
  • Pembaruan: Sistem menyimpan nilai baris sebelumnya dalam tabel riwayat dan mengatur nilai untuk ValidTo kolom ke waktu mulai transaksi saat ini (di zona waktu UTC) berdasarkan jam sistem. Ini menandai baris sebagai tertutup, dengan titik yang direkam yang barisnya valid. Dalam tabel saat ini, baris diperbarui dengan nilai barunya dan sistem mengatur nilai untuk ValidFrom kolom ke waktu mulai untuk transaksi (di zona waktu UTC) berdasarkan jam sistem. Nilai untuk baris yang diperbarui dalam tabel saat ini untuk ValidTo kolom tetap menjadi nilai 9999-12-31maksimum .
  • Hapus: Sistem menyimpan nilai baris sebelumnya dalam tabel riwayat dan mengatur nilai untuk ValidTo kolom ke waktu mulai transaksi saat ini (di zona waktu UTC) berdasarkan jam sistem. Ini menandai baris sebagai tertutup, dengan titik yang direkam di mana baris sebelumnya valid. Dalam tabel saat ini, baris dihapus. Kueri tabel saat ini tidak mengembalikan baris ini. Hanya kueri yang menangani data riwayat yang mengembalikan data yang barisnya ditutup.
  • Penggabungan: Operasi berperilaku persis seolah-olah hingga tiga pernyataan ( INSERT, , UPDATEdan/atau ) DELETEdijalankan, tergantung pada apa yang ditentukan sebagai tindakan dalam MERGE pernyataan.

Penting

Waktu yang dicatat dalam kolom datetime2 sistem didasarkan pada waktu mulai transaksi itu sendiri. Misalnya, semua baris yang dimasukkan dalam satu transaksi memiliki waktu UTC yang sama yang dicatat dalam kolom yang sesuai dengan awal SYSTEM_TIME periode.

Catatan

Saat Anda menjalankan kueri modifikasi data pada tabel temporal, Mesin Database menambahkan baris ke tabel riwayat meskipun tidak ada nilai kolom yang berubah.

Bagaimana cara mengkueri data temporal?

Pernyataan SELECT ... FROM <table> ini memiliki klausa FOR SYSTEM_TIMEbaru , dengan lima subklaus khusus temporal untuk mengkueri data di seluruh tabel riwayat dan saat ini. Sintaks pernyataan baru SELECT ini didukung langsung pada satu tabel, disebarluaskan melalui beberapa gabungan, dan melalui tampilan di atas beberapa tabel temporal.

Saat Anda mengkueri menggunakan FOR SYSTEM_TIME klausa menggunakan salah satu dari lima subklaus, data historis dari tabel temporal disertakan, seperti yang diperlihatkan dalam gambar berikut.

Diagram showing how Temporal Querying works.

Kueri berikut mencari versi baris untuk karyawan dengan kondisi WHERE EmployeeID = 1000 filter yang aktif setidaknya untuk sebagian periode antara 1 Januari 2021 dan 1 Januari 2022 (termasuk batas atas):

SELECT * FROM Employee
    FOR SYSTEM_TIME
        BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-01-01 00:00:00.0000000'
            WHERE EmployeeID = 1000 ORDER BY ValidFrom;

Catatan

FOR SYSTEM_TIME memfilter baris yang memiliki periode validitas dengan durasi nol (ValidFrom = ValidTo).

Baris tersebut akan dihasilkan jika Anda melakukan beberapa pembaruan pada kunci primer yang sama dalam transaksi yang sama. Dalam hal ini, kueri temporal hanya mengembalikan versi baris sebelum transaksi, dan baris saat ini setelah transaksi.

Jika Anda perlu menyertakan baris tersebut dalam analisis, kueri tabel riwayat secara langsung.

Dalam tabel berikut, ValidFrom di kolom Baris yang Memenuhi Syarat mewakili nilai dalam ValidFrom kolom dalam tabel yang sedang dikueri dan ValidTo mewakili nilai dalam ValidTo kolom dalam tabel yang sedang dikueri. Untuk sintaks lengkap dan misalnya, lihat FROM (Transact-SQL) dan Mengkueri data dalam tabel temporal versi sistem.

Expression Baris yang Memenuhi Syarat Catatan
AS OFdate_time ValidFrom <=AND ValidTo >date_time date_time Mengembalikan tabel dengan baris yang berisi nilai saat ini pada titik waktu yang ditentukan di masa lalu. Secara internal, penyatuan dilakukan antara tabel temporal dan tabel riwayatnya dan hasilnya difilter untuk mengembalikan nilai dalam baris yang valid pada titik waktu yang ditentukan oleh parameter date_time . Nilai untuk baris dianggap valid jika nilai system_start_time_column_name kurang dari atau sama dengan nilai parameter date_time dan nilai system_end_time_column_name lebih besar dari nilai parameter date_time .
FROMTOend_date_time start_date_time ValidFrom <AND ValidTo >start_date_time end_date_time Mengembalikan tabel dengan nilai untuk semua versi baris yang aktif dalam rentang waktu yang ditentukan, terlepas dari apakah mereka mulai aktif sebelum nilai parameter start_date_time untuk FROM argumen atau berhenti aktif setelah nilai parameter end_date_time untuk TO argumen. Secara internal, penyatuan dilakukan antara tabel temporal dan tabel riwayatnya. Hasilnya difilter untuk mengembalikan nilai untuk semua versi baris yang aktif kapan saja selama rentang waktu yang ditentukan. Baris yang berhenti aktif persis di batas bawah yang ditentukan oleh FROM titik akhir tidak disertakan, dan rekaman yang menjadi aktif persis pada batas atas yang ditentukan oleh TO titik akhir juga tidak disertakan.
BETWEENANDend_date_time start_date_time ValidFrom <=AND ValidTo >start_date_time end_date_time Sama seperti sebelumnya dalam FOR SYSTEM_TIME FROMdeskripsi end_date_time start_date_timeTO, kecuali tabel baris yang dikembalikan menyertakan baris yang menjadi aktif pada batas atas yang ditentukan oleh titik akhir end_date_time.
CONTAINED IN (start_date_time, end_date_time) ValidFrom >=AND ValidTo <=end_date_time start_date_time Mengembalikan tabel dengan nilai untuk semua versi baris yang dibuka dan ditutup dalam rentang waktu yang ditentukan oleh dua nilai periode untuk CONTAINED IN argumen. Baris yang menjadi aktif tepat pada batas bawah atau berhenti aktif tepat di batas atas disertakan.
ALL Semua baris Mengembalikan union baris yang termasuk dalam tabel saat ini dan riwayat.

Sembunyikan kolom titik

Anda dapat memilih untuk menyembunyikan kolom titik, sehingga kueri yang tidak secara eksplisit mereferensikannya tidak mengembalikan kolom ini (misalnya, saat menjalankan SELECT * FROM <table>).

Untuk mengembalikan kolom tersembunyi, Anda harus secara eksplisit merujuk ke kolom tersembunyi dalam kueri. Demikian INSERT pula dan BULK INSERT pernyataan berlanjut seolah-olah kolom periode baru ini tidak ada (dan nilai kolom diisi secara otomatis).

Untuk detail tentang menggunakan HIDDEN klausa, lihat CREATE TABLE dan ALTER TABLE.

Sampel

ASP.NET

Lihat aplikasi web ASP.NET Core ini untuk mempelajari cara membangun aplikasi temporal menggunakan tabel temporal.

Mengunduh database sampel Adventure Works

Anda dapat mengunduh database AdventureWorks untuk SQL Server, yang mencakup fitur tabel temporal.

Konten terkait