Bagikan melalui

Mengubah data dalam tabel temporal versi sistem

  • Artikel

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

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. Saat data diperbarui, data di-versi, dan versi sebelumnya dari setiap baris yang diperbarui disisipkan ke dalam tabel riwayat. Saat data dihapus, penghapusannya logis, dan baris dipindahkan ke tabel riwayat dari tabel saat ini; data tidak dihapus secara permanen.

Menyisipkan 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

Anda dapat membuat INSERT pernyataan saat Anda memiliki kolom yang terlihat PERIOD sebagai berikut, untuk memperhitungkan PERIOD kolom:

Jika Anda menentukan daftar kolom dalam pernyataan, INSERT Anda dapat menghilangkan PERIOD kolom karena sistem 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 PERIOD kolom ditentukan sebagai HIDDEN, Anda tidak perlu memperhitungkan PERIOD kolom dalam pernyataan Anda INSERT . 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 menggunakan PARTITION SWITCH sebagai mekanisme yang efisien untuk memuat data ke dalam partisi kosong, atau untuk memuat ke dalam beberapa partisi secara paralel.

Tabel penahapan yang digunakan dalam PARTITION SWITCH IN pernyataan dengan tabel temporal harus ditentukan SYSTEM_TIME PERIOD , tetapi tidak perlu menjadi 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 titik, 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.

Memperbarui 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 diperbarui ManagerID 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 dengan 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 sementara 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

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

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 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