Bagikan melalui

Membuat tabel temporal versi sistem

Berlaku untuk:SQL Server 2016 (13.x) dan versi yang lebih baru Azure SQL Database Azure SQL Managed InstanceSQL database di Microsoft Fabric

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 oleh pengguna yang dibuat sebelumnya: Anda membuat tabel riwayat yang paling sesuai dengan kebutuhan Anda lalu mereferensikan tabel tersebut selama proses 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 yang diaktifkan versi sistemnya dan 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 elemen PERIOD FOR SYSTEM_TIME dengan dua kolom datetime2, dideklarasikan sebagai GENERATED ALWAYS AS ROW START atau GENERATED ALWAYS AS ROW END.

Kolom PERIOD selalu diasumsikan tidak dapat bernilai null, bahkan jika nullability tidak ditentukan. Jika kolom secara eksplisit didefinisikan sebagai nullable, pernyataan tersebut 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).

Dalam database Fabric SQL, tabel riwayat yang dibuat tidak dicerminkan ke Fabric OneLake.

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, sebuah tabel baru dibuat dengan versi sistem diaktifkan dan nama tabel riwayat ditentukan secara jelas.

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, sebuah indeks kolom penyimpanan berkluster dan sebuah indeks tambahan rowstore (B-tree) nonkluster dibuat untuk melakukan 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 ketika merujuk pada 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 penangkapan data perubahan, pelacakan perubahan, replikasi transaksional, atau replikasi penggabungan.

Dalam database Fabric SQL dan di Azure SQL Database yang telah dikonfigurasi untuk pencerminan Fabric, ketika Anda menggunakan tabel yang ada sebagai tabel riwayat selama pembuatan tabel temporal, tabel yang ada akan berhenti dicerminkan.

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 datetime2ValidFrom dan ValidTo) agar tidak memengaruhi aplikasi yang ada yang tidak secara eksplisit menentukan nama kolom (misalnya, SELECT * atau INSERT tanpa daftar kolom) karena tidak dirancang untuk menangani kolom baru.

Menambahkan 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 dasar.

Keterangan

Menambahkan kolom yang tidak boleh kosong dengan default ke tabel yang ada dengan data adalah operasi berskala besar pada data pada semua edisi kecuali 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 awal periode dan akhir periode harus dipilih dengan hati-hati.

  • Pengaturan default untuk kolom awal menentukan dari titik waktu mana Anda menganggap baris yang ada sah. Ini tidak bisa ditetapkan sebagai titik tanggal waktu 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.

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

Ketika tabel riwayat yang ada ditentukan saat mengaktifkan SYSTEM_VERSIONING, pemeriksaan konsistensi data dilakukan pada baik tabel saat ini maupun tabel riwayat. 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, hapus pemicu pada tabel temporal masa mendatang. Kemudian, pastikan PERIOD kolom tidak boleh bernilai 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 definisi PERIOD secara implisit menyebabkan perubahan generated_always_type menjadi AS_ROW_START dan AS_ROW_END untuk kolom tersebut.

Penambahan PERIOD melakukan pemeriksaan konsistensi data pada tabel yang sedang digunakan 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