Mengubah data dalam 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

Data dalam tabel temporal versi sistem dimodifikasi menggunakan pernyataan bahasa manipulasi data reguler (DML), dengan satu perbedaan penting: data kolom periode tidak dapat dimodifikasi secara langsung. Ketika data diperbarui, versinya juga diperbarui, dan versi sebelumnya dari setiap baris yang diperbarui dimasukkan ke dalam tabel riwayat. Ketika data dihapus, penghapusan tersebut logis, dan baris dipindahkan ke tabel riwayat dari tabel saat ini; Data tidak dihapus secara permanen.

Sisipkan data

Saat Anda menyisipkan data baru, Anda perlu memperhitungkan PERIOD kolom jika bukan HIDDEN. Anda juga dapat menggunakan pengalihan partisi dengan tabel temporal.

Sisipkan data baru dengan kolom periode yang terlihat

Susun pernyataan Anda INSERT untuk memperhitungkan kolom-kolom yang terlihat PERIOD sebagai berikut:

Jika Anda menentukan daftar kolom dalam pernyataan INSERT, Anda dapat menghilangkan kolom PERIOD karena sistem akan menghasilkan nilai untuk kolom ini secara otomatis.

-- Insert with column list and without period columns
INSERT INTO [dbo].[Department]
(
    [DeptID],
    [DeptName],
    [ManagerID],
    [ParentDeptID]
)
VALUES (10, 'Marketing', 101, 1);

Jika Anda menentukan PERIOD kolom dalam daftar kolom dalam pernyataan Anda INSERT , maka Anda perlu menentukan DEFAULT sebagai nilainya.

INSERT INTO [dbo].[Department]
(
    DeptID,
    DeptName,
    ManagerID,
    ParentDeptID,
    ValidFrom,
    ValidTo
)
VALUES (11, 'Sales', 101, 1, DEFAULT, DEFAULT);

Jika Anda tidak menentukan daftar kolom dalam pernyataan Anda INSERT , tentukan DEFAULT untuk PERIOD kolom.

-- Insert without a column list and DEFAULT values for period columns
INSERT INTO [dbo].[Department]
VALUES (
    12,
    'Production',
    101,
    1,
    DEFAULT,
    DEFAULT
);

Menyisipkan data ke dalam tabel dengan kolom periode TERSEMBUNYI

Jika kolom PERIOD ditentukan sebagai HIDDEN, Anda tidak perlu memperhitungkan kolom PERIOD dalam pernyataan INSERT Anda. Perilaku ini menjamin bahwa aplikasi warisan Anda terus berfungsi saat Anda mengaktifkan penerapan versi sistem pada tabel yang mendapat manfaat dari penerapan versi.

CREATE TABLE [dbo].[CompanyLocation]
(
    [LocID] INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
    [LocName] VARCHAR (50) NOT NULL,
    [City] VARCHAR (50) NOT NULL,
    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
)
WITH (SYSTEM_VERSIONING = ON);
GO

INSERT INTO [dbo].[CompanyLocation]
VALUES ('Headquarters', 'New York');

Menyisipkan data menggunakan PARTITION SWITCH

Jika tabel saat ini dipartisi, Anda dapat menggunakannya PARTITION SWITCH untuk memuat data secara efisien ke dalam partisi kosong, atau untuk memuat ke beberapa partisi secara paralel.

Tabel staging yang digunakan dalam pernyataan PARTITION SWITCH IN dengan tabel temporal harus memiliki SYSTEM_TIME PERIOD yang didefinisikan, tetapi tidak harus digunakan sebagai tabel temporal. Ini memastikan bahwa pemeriksaan konsistensi temporal dilakukan selama penyisipan data ke dalam tabel penahapan atau ketika SYSTEM_TIME periode ditambahkan ke tabel penahapan yang telah diisi sebelumnya.

/* Create staging table with period definition for SWITCH IN temporal table */
CREATE TABLE [dbo].[Staging_Department_Partition2]
(
    [DeptID] INT NOT NULL,
    [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])
) ON [PRIMARY];

/* Create aligned primary key */
ALTER TABLE [dbo].[Staging_Department_Partition2]
    ADD CONSTRAINT [Staging_Department_Partition2_PK]
        PRIMARY KEY CLUSTERED ([DeptID] ASC) ON [PRIMARY];

/*
Create and enforce constraints for partition boundaries.
Partition 2 contains rows with DeptID > 100 and DeptID <=200
*/
ALTER TABLE [dbo].[Staging_Department_Partition2] WITH CHECK
    ADD CONSTRAINT [chk_staging_Department_partition_2]
            CHECK ([DeptID] > N'100' AND [DeptID] <= N'200');

ALTER TABLE [dbo].[Staging_Department_Partition2]
    CHECK CONSTRAINT [chk_staging_Department_partition_2];

/*Load data into staging table*/
INSERT INTO [dbo].[staging_Department] (
    [DeptID],
    [DeptName],
    [ManagerID],
    [ParentDeptID]
)
VALUES (101, 'D101', 1, NULL);

/*Use PARTITION SWITCH IN to efficiently add data to current table */
ALTER TABLE [Staging_Department]
    SWITCH TO [dbo].[Department] PARTITION 2;

Jika Anda mencoba melakukan PARTITION SWITCH dari tabel tanpa definisi periode, Anda mendapatkan pesan kesalahan.

Msg 13577, Level 16, State 1, Line 25 ALTER TABLE SWITCH statement failed on table 'MyDB.dbo.Staging_Department_2015_09_26' because target table has SYSTEM_TIME PERIOD while source table does not have it.

Perbarui data

Anda memperbarui data dalam tabel saat ini dengan pernyataan reguler UPDATE . Anda dapat memperbarui data dalam tabel saat ini dari tabel riwayat untuk skenario bencana. Namun, Anda tidak dapat memperbarui PERIOD kolom dan Anda tidak dapat langsung memperbarui data dalam tabel riwayat saat SYSTEM_VERSIONING = ON.

Jika Anda mengatur SYSTEM_VERSIONING ke OFF dan memperbarui baris dari tabel saat ini dan riwayat, sistem tidak mempertahankan riwayat perubahan.

Memperbarui tabel saat ini

Dalam contoh ini, kolom ManagerID diperbarui untuk setiap baris di mana DeptID adalah 10. Kolom PERIOD tidak dirujuk dengan cara apa pun.

UPDATE [dbo].[Department]
SET [ManagerID] = 501
WHERE [DeptID] = 10;

Namun, Anda tidak dapat memperbarui PERIOD kolom, dan Anda tidak dapat memperbarui tabel riwayat. Dalam contoh ini, upaya untuk memperbarui PERIOD kolom menghasilkan kesalahan.

UPDATE [dbo].[Department]
    SET ValidFrom = '2015-09-23 23:48:31.2990175'
WHERE DeptID = 10;

Pernyataan menghasilkan kesalahan berikut.

Msg 13537, Level 16, State 1, Line 3
Cannot update GENERATED ALWAYS columns in table 'TmpDev.dbo.Department'.

Memperbarui tabel saat ini dari tabel riwayat

Anda dapat menggunakan UPDATE pada tabel saat ini untuk mengembalikan status baris aktual ke status valid pada titik waktu tertentu di masa lalu. Anggap saja ini sebagai kembali ke versi baris terakhir yang diketahui baik. Contoh berikut menunjukkan kembali ke nilai dalam tabel riwayat per 25 April 2015, di mana DeptID adalah 10.

UPDATE Department
SET DeptName = History.DeptName
FROM Department FOR SYSTEM_TIME
    AS OF '2015-04-25' AS History
WHERE History.DeptID = 10
    AND Department.DeptID = 10;

Menghapus data

Anda menghapus data dalam tabel saat ini dengan pernyataan reguler DELETE . Kolom periode akhir untuk baris yang dihapus diisi dengan waktu mulai transaksi yang mendasar. Anda tidak dapat langsung menghapus baris dari tabel riwayat ketika SYSTEM_VERSIONING adalah ON. Jika Anda mengatur SYSTEM_VERSIONING = OFF dan menghapus baris dari tabel saat ini dan riwayat, sistem tidak mempertahankan riwayat perubahan.

Pernyataan berikut tidak didukung saat SYSTEM_VERSIONING = ON:

  • TRUNCATE
  • SWITCH PARTITION OUT untuk tabel saat ini
  • SWITCH PARTITION IN untuk tabel riwayat

Gunakan MERGE untuk mengubah data dalam tabel temporal

Operasi MERGE didukung dengan batasan yang sama yang dimiliki oleh pernyataan INSERT dan UPDATE, mengenai kolom PERIOD.

CREATE TABLE DepartmentStaging
(
    DeptId INT,
    DeptName VARCHAR (50)
);
GO

INSERT INTO DepartmentStaging
VALUES (1, 'Company Management');

INSERT INTO DepartmentStaging
VALUES (10, 'Science & Research');

INSERT INTO DepartmentStaging
VALUES (15, 'Process Management');

MERGE INTO dbo.Department AS target
USING (
    SELECT DeptId, DeptName
    FROM DepartmentStaging
    ) AS source(DeptId, DeptName)
    ON (target.DeptId = source.DeptId)
WHEN MATCHED
    THEN UPDATE SET DeptName = source.DeptName
WHEN NOT MATCHED
    THEN
        INSERT (DeptId, DeptName)
        VALUES (source.DeptId, source.DeptName);

Konten terkait

  • Last updated on