Bagikan melalui

Membuat tabel temporal versi sistem

  • Artikel

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

Ada tiga cara untuk membuat tabel temporal versi sistem saat mempertimbangkan bagaimana tabel riwayat ditentukan:

  • Tabel temporal dengan tabel riwayat anonim: Anda menentukan skema tabel saat ini dan membiarkan sistem membuat tabel riwayat yang sesuai dengan nama yang dibuat secara otomatis.

  • Tabel temporal dengan tabel riwayat default: Anda menentukan nama skema tabel riwayat dan nama tabel dan membiarkan sistem membuat tabel riwayat dalam skema tersebut.

  • Tabel temporal dengan tabel riwayat yang ditentukan pengguna yang dibuat sebelumnya: Anda membuat tabel riwayat yang paling sesuai dengan kebutuhan Anda lalu mereferensikan tabel tersebut selama pembuatan tabel temporal.

Membuat tabel temporal dengan tabel riwayat anonim

Membuat tabel temporal dengan tabel riwayat anonim adalah opsi yang nyaman untuk pembuatan objek cepat, terutama dalam prototipe dan lingkungan pengujian. Ini juga cara paling sederhana untuk membuat tabel temporal karena tidak memerlukan parameter apa pun dalam SYSTEM_VERSIONING klausul. Dalam contoh berikut, tabel baru dibuat dengan penerapan versi sistem diaktifkan tanpa menentukan nama tabel riwayat.

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);

Keterangan

Tabel temporal versi sistem harus memiliki kunci utama yang ditentukan dan memiliki persis satu PERIOD FOR SYSTEM_TIME yang ditentukan dengan dua kolom datetime2 , dinyatakan sebagai GENERATED ALWAYS AS ROW START atau GENERATED ALWAYS AS ROW END.

Kolom PERIOD selalu diasumsikan tidak dapat diubah ke null, bahkan jika nullability tidak ditentukan. PERIOD Jika kolom secara eksplisit didefinisikan sebagai nullable, CREATE TABLE pernyataan gagal.

Tabel riwayat harus selalu diratakan skema dengan tabel saat ini atau temporal, sehubungan dengan jumlah kolom, nama kolom, pengurutan, dan jenis data.

Tabel riwayat anonim secara otomatis dibuat dalam skema yang sama dengan tabel saat ini atau temporal.

Nama tabel riwayat anonim memiliki format berikut: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_<suffix>. Akhiran bersifat opsional, dan ditambahkan hanya jika bagian pertama dari nama tabel tidak unik.

Tabel riwayat dibuat sebagai tabel rowstore. PAGE pemadatan diterapkan jika memungkinkan, jika tidak, tabel riwayat tidak dikompresi. Misalnya, beberapa konfigurasi tabel, seperti SPARSE kolom, tidak mengizinkan pemadatan.

Indeks berkluster default dibuat untuk tabel riwayat dengan nama yang dibuat secara otomatis dalam format IX_<history_table_name>. Indeks berkluster berisi PERIOD kolom (akhir, mulai).

Untuk membuat tabel saat ini sebagai tabel yang dioptimalkan memori, lihat Tabel temporal versi sistem dengan tabel yang dioptimalkan memori.

Membuat tabel temporal dengan tabel riwayat default

Membuat tabel temporal dengan tabel riwayat default adalah opsi yang nyaman saat Anda ingin mengontrol penamaan, dan masih mengandalkan sistem untuk membuat tabel riwayat dengan konfigurasi default. Dalam contoh berikut, tabel baru dibuat dengan penerapan versi sistem diaktifkan dengan nama tabel riwayat yang ditentukan secara eksplisit.

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

Keterangan

Tabel riwayat dibuat menggunakan aturan yang sama seperti yang berlaku untuk membuat tabel riwayat "anonim", dengan aturan berikut yang berlaku khusus untuk tabel riwayat bernama.

  • Nama skema wajib untuk HISTORY_TABLE parameter .

  • Jika skema yang ditentukan tidak ada, CREATE TABLE pernyataan gagal.

  • Jika tabel yang ditentukan oleh HISTORY_TABLE parameter sudah ada, tabel tersebut memvalidasi terhadap tabel temporal yang baru dibuat dalam hal konsistensi skema dan konsistensi data temporal. Jika Anda menentukan tabel riwayat yang tidak valid, CREATE TABLE pernyataan gagal.

Membuat tabel temporal dengan tabel riwayat yang ditentukan pengguna

Membuat tabel temporal dengan tabel riwayat yang ditentukan pengguna adalah opsi yang nyaman ketika pengguna ingin menentukan tabel riwayat dengan opsi penyimpanan tertentu dan indeks berbeda yang disetel ke kueri historis. Dalam contoh berikut, tabel riwayat yang ditentukan pengguna dibuat dengan skema yang selaras dengan tabel temporal yang dibuat. Untuk tabel riwayat yang ditentukan pengguna ini, indeks penyimpan kolom berkluster dan indeks rowstore (B-tree) tambahan dibuat untuk pencarian titik. Setelah tabel riwayat yang ditentukan pengguna ini dibuat, tabel temporal dibuat yang menentukan tabel riwayat yang ditentukan pengguna sebagai tabel riwayat default.

Catatan

Dokumentasi menggunakan istilah pohon B umumnya dalam referensi ke indeks. Dalam indeks rowstore, Mesin Database mengimplementasikan pohon B+. Ini tidak berlaku untuk indeks penyimpan kolom atau indeks pada tabel yang dioptimalkan memori. Untuk informasi selengkapnya, lihat panduan arsitektur dan desain indeks SQL Server dan Azure SQL.

CREATE TABLE DepartmentHistory
(
    DeptID INT NOT NULL,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 NOT NULL,
    ValidTo DATETIME2 NOT NULL
);
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory ON DepartmentHistory;

CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_Period_Columns
ON DepartmentHistory (ValidTo, ValidFrom, DeptID);
GO

CREATE TABLE Department
(
    DeptID int NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

Keterangan

Jika Anda berencana untuk menjalankan kueri analitik pada data historis yang menggunakan fungsi agregat atau windowing, membuat penyimpan kolom berkluster sebagai indeks utama sangat disarankan untuk kompresi dan performa kueri.

Jika Anda berencana menggunakan tabel temporal untuk audit data (yaitu, mencari perubahan historis untuk satu baris dari tabel saat ini), Anda harus membuat tabel riwayat rowstore dengan indeks berkluster.

Tabel riwayat tidak dapat memiliki kunci utama, kunci asing, indeks unik, batasan tabel, atau pemicu. Ini tidak dapat dikonfigurasi untuk mengubah penangkapan data, pelacakan perubahan, replikasi transaksional, atau replikasi penggabungan.

Mengubah tabel non-temporal menjadi tabel temporal versi sistem

Anda dapat mengaktifkan penerapan versi sistem pada tabel non-temporal yang ada, seperti ketika Anda ingin memigrasikan solusi temporal kustom ke dukungan bawaan.

Misalnya, Anda mungkin memiliki sekumpulan tabel tempat penerapan versi diimplementasikan dengan pemicu. Menggunakan penerapan versi sistem temporal kurang kompleks dan memberikan manfaat lain termasuk:

  • Riwayat yang tidak dapat diubah
  • Sintaks baru untuk kueri perjalanan waktu
  • Performa DML yang lebih baik
  • Biaya pemeliharaan minimal

Saat mengonversi tabel yang sudah ada, pertimbangkan untuk menggunakan HIDDEN klausul untuk menyembunyikan kolom baru PERIOD (kolom ValidFrom datetime2 dan ValidTo) untuk menghindari memengaruhi aplikasi yang ada yang tidak secara eksplisit menentukan nama kolom (misalnya, SELECT * atau INSERT tanpa daftar kolom) tidak dirancang untuk menangani kolom baru.

Menambahkan penerapan versi ke tabel non-temporal

Jika Anda ingin mulai melacak perubahan untuk tabel non-temporal yang berisi data, Anda perlu menambahkan PERIOD definisi dan secara opsional memberikan nama untuk tabel riwayat kosong yang dibuat SQL Server untuk Anda:

CREATE SCHEMA History;
GO

ALTER TABLE InsurancePolicy ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_InsurancePolicy_ValidFrom DEFAULT SYSUTCDATETIME(),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DF_InsurancePolicy_ValidTo DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);
GO

ALTER TABLE InsurancePolicy
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.InsurancePolicy));
GO

Penting

Presisi untuk DATETIME2 harus selaras dengan presisi untuk tabel yang mendasarinya.

Keterangan

Menambahkan kolom yang tidak dapat dinon-null dengan default ke tabel yang ada dengan data adalah ukuran operasi data pada semua edisi selain edisi SQL Server Enterprise (di mana itu adalah operasi metadata). Dengan tabel riwayat besar yang ada dengan data pada edisi Standar SQL Server, menambahkan kolom non-null bisa menjadi operasi yang mahal.

Batasan untuk kolom akhir periode mulai dan titik harus dipilih dengan hati-hati:

  • Default untuk kolom mulai menentukan dari titik waktu mana Anda menganggap baris yang ada valid. Ini tidak dapat ditentukan sebagai titik tanggalwaktu di masa mendatang.

  • Waktu akhir harus ditentukan sebagai nilai maksimum untuk presisi datetime2 tertentu, misalnya 9999-12-31 23:59:59 atau 9999-12-31 23:59:59.9999999.

PERIOD Menambahkan melakukan pemeriksaan konsistensi data pada tabel saat ini untuk memastikan bahwa nilai yang ada untuk kolom periode valid.

Ketika tabel riwayat yang ada ditentukan saat mengaktifkan SYSTEM_VERSIONING, pemeriksaan konsistensi data dilakukan di seluruh tabel riwayat dan saat ini. Ini dapat dilewati jika Anda menentukan DATA_CONSISTENCY_CHECK = OFF sebagai parameter tambahan.

Memigrasikan tabel yang ada ke dukungan bawaan

Contoh ini menunjukkan cara bermigrasi dari solusi yang ada berdasarkan pemicu ke dukungan temporal bawaan. Untuk contoh ini, kami berasumsi bahwa solusi kustom saat ini membagi data saat ini dan historis dalam dua tabel pengguna terpisah (ProjectTaskCurrent dan ProjectTaskHistory).

Jika solusi Anda yang ada menggunakan tabel tunggal untuk menyimpan baris aktual dan historis, maka Anda harus membagi data menjadi dua tabel sebelum langkah-langkah migrasi yang diperlihatkan dalam contoh berikut. Pertama, jatuhkan pemicu pada tabel temporal di masa depan. Kemudian, pastikan PERIOD kolom tidak dapat diubah ke null.

/* Drop trigger on future temporal table */
DROP TRIGGER ProjectCurrent_OnUpdateDelete;

/* Make sure that future period columns are non-nullable */
ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent ADD PERIOD
FOR SYSTEM_TIME([ValidFrom], [ValidTo]);

ALTER TABLE ProjectTaskCurrent SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.ProjectTaskHistory,
        DATA_CONSISTENCY_CHECK = ON
    )
);

Keterangan

Mereferensikan kolom yang ada dalam PERIOD definisi secara implisit berubah generated_always_type menjadi AS_ROW_START dan AS_ROW_END untuk kolom tersebut.

PERIOD Menambahkan melakukan pemeriksaan konsistensi data pada tabel saat ini untuk memastikan bahwa nilai yang ada untuk kolom periode valid.

Kami sangat menyarankan Anda mengatur SYSTEM_VERSIONING dengan DATA_CONSISTENCY_CHECK = ON, untuk memberlakukan pemeriksaan konsistensi data pada data yang ada.

Jika kolom tersembunyi lebih disukai, gunakan perintah ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;.

Konten terkait