Bagikan melalui

Membuat tabel temporal versi sistem

  • Artikel

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

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 dihasilkan 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 primer yang ditentukan dan memiliki tepat 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, meskipun nullability tidak ditentukan. PERIOD Jika kolom secara eksplisit didefinisikan sebagai nullable, CREATE TABLE pernyataan akan 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>_[akhiran]. Akhiran bersifat opsional dan ditambahkan hanya jika bagian pertama dari nama tabel tidak unik.

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

  • Indeks berkluster default dibuat untuk tabel riwayat dengan nama yang dihasilkan 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 Memory-Optimized.

Membuat tabel temporal dengan tabel riwayat default

Membuat tabel temporal dengan tabel riwayat default adalah opsi yang nyaman ketika 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 akan gagal.
  • Jika tabel yang ditentukan oleh HISTORY_TABLE parameter sudah ada, tabel tersebut divalidasi terhadap tabel temporal yang baru dibuat dalam hal konsistensi skema dan konsistensi data temporal. Jika Anda menentukan tabel riwayat yang tidak valid, pernyataan akan CREATE TABLE 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 non-kluster tambahan (pohon B+) dibuat untuk pencarian titik. Setelah tabel riwayat yang ditentukan pengguna ini dibuat, tabel temporal versi sistem dibuat menentukan tabel riwayat yang ditentukan pengguna sebagai tabel riwayat default.

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 kasus penggunaan utama adalah untuk audit data (yaitu, mencari perubahan historis untuk satu baris dari tabel saat ini), maka pilihan yang baik adalah membuat tabel riwayat rowstore dengan indeks berkluster
  • Tabel riwayat tidak dapat memiliki kunci primer, kunci asing, indeks unik, batasan tabel, atau pemicu. Ini tidak dapat dikonfigurasi untuk mengubah tangkapan data, pelacakan perubahan, replikasi transaksional atau penggabungan.

Mengubah tabel non-temporal menjadi tabel temporal versi sistem

Anda dapat mengaktifkan penerapan versi sistem pada tabel non-temporal yang ada, seperti saat 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 ValidFromdatetime2 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 SQL Server buat 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. Lihat keterangan berikut.

Keterangan

  • Menambahkan kolom yang tidak dapat diubah ke null dengan default ke tabel yang ada dengan data adalah ukuran operasi data pada semua edisi selain edisi SQL Server Enterprise (di mana kolom tersebut adalah operasi metadata). Dengan tabel riwayat besar yang ada dengan data pada SQL Server edisi Standar, menambahkan kolom non-null bisa menjadi operasi yang mahal.
  • Batasan untuk kolom awal periode dan akhir periode 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.
  • Saat 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 sudah 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, letakkan 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
  • Sangat disarankan untuk 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;.

Langkah berikutnya