Membuat tabel temporal versi sistem
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 SQL Server menggunakan istilah pohon B umumnya dalam referensi ke indeks. Dalam indeks rowstore, SQL Server mengimplementasikan pohon B+. Ini tidak berlaku untuk indeks penyimpan kolom atau penyimpanan data dalam 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
atau9999-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
- Tabel temporal
- Mulai menggunakan tabel temporal versi sistem
- Mengelola retensi data historis dalam tabel temporal versi sistem
- Tabel temporal versi sistem dengan tabel memori yang dioptimalkan
- BUAT TABEL (Transact-SQL)
- Mengubah data dalam tabel temporal versi sistem
- Mengkueri data dalam tabel temporal versi sistem
- Mengubah skema tabel temporal versi sistem
- Menghentikan penerapan versi sistem pada tabel temporal versi sistem