Memodifikasi data dalam tabel temporal versi sistem

Artikel
05/23/2023

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

Data dalam tabel temporal versi sistem dimodifikasi menggunakan pernyataan DML reguler dengan satu perbedaan penting: data kolom periode tidak dapat dimodifikasi secara langsung. Saat data diperbarui, data diversi, dengan versi sebelumnya dari setiap baris yang diperbarui disisipkan ke dalam tabel riwayat. Saat data dihapus, penghapusannya logis, dengan baris dipindahkan ke tabel riwayat dari tabel saat ini - tidak dihapus secara permanen.

Menyisipkan data

Saat Anda menyisipkan data baru, Anda perlu mempertanyakan kolom PERIOD jika tidak DISEMBUNYIKAN. Anda juga dapat menggunakan pengalihan partisi dengan tabel temporal versi sistem.

Sisipkan data baru dengan kolom periode yang terlihat

Anda bisa membuat pernyataan INSERT saat Anda memiliki kolom PERIOD yang terlihat sebagai berikut untuk memperhitungkan kolom PERIOD baru:

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 kolom PERIOD dalam daftar kolom dalam pernyataan INSERT Anda, 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 INSERT Anda, tentukan DEFAULT untuk kolom PERIOD .

  -- Insert without 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 titik TERSEMBUNYI

Jika kolom PERIOD ditentukan sebagai HIDDEN, maka Anda hanya perlu menentukan nilai untuk kolom yang terlihat saat Anda menggunakan INSERT tanpa menentukan daftar kolom. Anda tidak perlu memperkirakan kolom PERIOD baru dalam pernyataan INSERT Anda. Perilaku ini menjamin bahwa aplikasi warisan Anda akan terus berfungsi ketika Anda mengaktifkan penerapan versi sistem pada tabel yang akan 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 sakelar partisi 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 pernyataan PARTITION SWITCH IN dengan tabel temporal versi sistem harus memiliki SYSTEM_TIME PERIOD yang ditentukan, tetapi tidak perlu menjadi tabel temporal versi sistem. Ini memastikan bahwa pemeriksaan konsistensi temporal dilakukan selama penyisipan data ke dalam tabel penahapan atau ketika periode SYSTEM_TIME 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 akan 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 UPDATE reguler. Anda dapat memperbarui data dalam tabel saat ini dari tabel riwayat untuk skenario "ups". Namun, Anda tidak dapat memperbarui kolom PERIOD dan Anda tidak dapat langsung memperbarui data dalam tabel riwayat saat SYSTEM_VERSIONING = AKTIF.

Atur SYSTEM_VERSIONING = OFF dan perbarui baris dari tabel saat ini dan riwayat tetapi perlu diingat bahwa sistem tidak akan mempertahankan riwayat perubahan.

Memperbarui tabel saat ini

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

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

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

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

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 (kembali ke "versi baris terakhir yang diketahui baik"). Contoh berikut menunjukkan kembali ke nilai dalam tabel riwayat per 2015-04-25 di mana DeptID = 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 DELETE biasa. Kolom periode akhir untuk baris yang dihapus akan diisi dengan waktu mulai transaksi yang mendasar. Anda tidak dapat langsung menghapus baris dari tabel riwayat saat SYSTEM_VERSIONING = AKTIF. Atur SYSTEM_VERSIONING = NONAKTIF dan hapus baris dari tabel saat ini dan riwayat tetapi perlu diingat bahwa sistem tidak akan mempertahankan riwayat perubahan. TRUNCATE, SWITCH PARTITION OUT dari tabel saat ini dan TABEL riwayat SWITCH PARTITION IN tidak didukung saat SYSTEM_VERSIONING = AKTIF.

Menggunakan MERGE untuk mengubah data dalam tabel temporal

Operasi MERGE didukung dengan batasan yang sama dengan 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 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);

Bagikan melalui