Membuat tabel temporal versi sistem

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

Anda dapat membuat tabel temporal versi sistem dengan tiga cara, berdasarkan bagaimana Anda menentukan tabel riwayat:

  • 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 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 apapun dalam klausa tersebut SYSTEM_VERSIONING . Contoh berikut membuat tabel baru dengan versi sistem diaktifkan, tanpa mendefinisikan 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
);

Remarks

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.

Database Engine secara otomatis membuat tabel riwayat anonim dalam skema yang sama dengan tabel saat ini atau tabel 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 klaster default dibuat untuk tabel riwayat dengan nama yang dihasilkan 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. Contoh berikut membuat tabel baru dengan versi sistem diaktifkan, dengan nama tabel riwayat didefinisikan 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
    )
);

Remarks

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 praktis ketika Anda ingin menentukan tabel riwayat dengan opsi penyimpanan tertentu dan indeks berbeda yang disetel sesuai dengan kueri historis. Dalam contoh berikut, Anda membuat tabel riwayat yang ditentukan pengguna dengan skema yang selaras dengan tabel temporal. Tabel riwayat ini memiliki indeks columnstore terkluster dan indeks rowstore nonclustered (B-tree) tambahan untuk pencarian titik data. Setelah Anda membuat tabel riwayat, Anda membuat tabel temporal dan menentukan tabel riwayat yang ditentukan pengguna sebagai tabel riwayat default.

Note

Dokumentasi biasanya 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
    )
);

Remarks

Jika Anda berencana menjalankan kueri analitik pada data historis yang menggunakan agregat atau fungsi jendela, membuat clustered columnstore sebagai indeks utama sangat direkomendasikan untuk kompresi dan kinerja 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 versi sistem pada tabel non-temporal yang sudah ada, misalnya 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 klausa untuk HIDDEN menyembunyikan kolom baru PERIOD (kolom ValidFromdatetime2 dan ValidTo) agar tidak memengaruhi aplikasi yang sudah ada yang tidak secara eksplisit menentukan nama kolom (misalnya, SELECT * atau INSERT tanpa daftar kolom) dan 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

Important

Presisi untuk DATETIME2 harus selaras dengan presisi untuk tabel dasar.

Remarks

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 untuk 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. Contoh ini mengasumsikan bahwa solusi kustom saat ini membagi data saat ini dan data historis menjadi dua tabel pengguna terpisah (ProjectTaskCurrent dan ProjectTaskHistory).

Jika solusi Anda saat ini menggunakan satu tabel untuk menyimpan baris aktual dan historis, maka Anda harus membagi data menjadi dua tabel sebelum langkah migrasi yang ditunjukkan 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 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
        )
    );

Remarks

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.

Menambahkan PERIOD melakukan pemeriksaan konsistensi data pada tabel saat ini untuk memastikan 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 berikut:

ALTER TABLE [tableName]
    ALTER COLUMN [columnName] ADD HIDDEN;

Konten terkait

  • Last updated on