Sistem sürümüne sahip bir zamana bağlı tablo oluşturma

Şunlar için geçerlidir: SQL Server 2016 (13.x) ve sonraki sürümler Azure SQL VeritabanıAzure SQL Yönetilen ÖrneğiSQL database in Microsoft Fabric

Geçmiş tablosunu nasıl belirttiğinize bağlı olarak, sistem sürümlü bir zamansal tabloyu üç şekilde oluşturabilirsiniz:

  • Anonim geçmiş tablosuyla zamansal tablo: mevcut tablonun şemasını belirlersiniz ve sistemin otomatik oluşturulan bir isimle ilgili bir geçmiş tablosu oluşturmasına izin verirsiniz.

  • varsayılan geçmiş tablosuolan zaman tablosu: geçmiş tablosu şema adı ve tablo adını belirtirsiniz ve sistemin bu şemada bir geçmiş tablosu oluşturmasını sağlarsınız.

  • Önceden oluşturulmuş kullanıcı tanımlı geçmiş tablosu içeren zamana bağlı tablo: gereksinimlerinize en uygun bir geçmiş tablosu oluşturur ve zamansal tablo oluşturma sırasında bu tabloya başvurursunuz.

Anonim geçmiş tablosuyla zamansal tablo oluşturma

anonim geçmiş tablosuyla zamansal tablo oluşturmak, özellikle prototiplerde ve test ortamlarında hızlı nesne oluşturmak için kullanışlı bir seçenektir. Ayrıca, cümlede SYSTEM_VERSIONING herhangi bir parametre gerektirmediği için zaman tablosu oluşturmanın en basit yoludur. Aşağıdaki örnek, geçmiş tablosunun adını tanımlamadan sistem sürümlemesi etkinleştirilmiş yeni bir tablo oluşturur.

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

Sistem sürümlü bir zamansal tablonun birincil anahtarı tanımlanmış olmalı ve PERIOD FOR SYSTEM_TIME, iki datetime2 sütunu içerecek şekilde tanımlanmalı, ayrıca GENERATED ALWAYS AS ROW START veya GENERATED ALWAYS AS ROW ENDolarak belirtilmelidir.

nullability belirtilmemiş olsa bile PERIOD sütunlarının her zaman non-null olarak varsayıldığı kabul edilir. PERIOD sütunları açıkça boş değer alabilir olarak tanımlanırsa, CREATE TABLE komutu başarısız olur.

Geçmiş tablosu her zaman sütun, sütun adı, sıralama ve veri türü sayısına göre geçerli veya zamana bağlı tabloyla hizalanmalıdır.

Database Engine, mevcut veya zamansal tabloyla aynı şemada otomatik olarak anonim bir geçmiş tablosu oluşturur.

Anonim geçmiş tablosu adı şu biçimdedir: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_<suffix>. Soneki isteğe bağlıdır ve yalnızca tablo adının ilk bölümü benzersiz değilse eklenir.

Geçmiş tablosu bir satır deposu tablosu olarak oluşturulur. mümkünse PAGE sıkıştırma uygulanır, aksi takdirde geçmiş tablosu sıkıştırılmaz. Örneğin, SPARSE sütunları gibi bazı tablo yapılandırmaları sıkıştırmaya izin vermez.

Geçmiş tablosu için, IX_<history_table_name> biçiminde otomatik oluşturulmuş bir adla varsayılan bir kümelenmiş dizin oluşturulur. Kümelenmiş dizin PERIOD sütunlarını içerir (end, start).

Doku SQL veritabanında, oluşturulan geçmiş tablosu Doku OneLake'e yansıtılamaz.

Geçerli tabloyu bellek için iyileştirilmiş bir tablo olarak oluşturmak için, bellek için iyileştirilmiş tablolarla sistem sürümüne sahip zamana bağlı tablolar konusuna bkz. .

Varsayılan geçmiş tablosuyla zamana bağlı tablo oluşturma

varsayılan geçmiş tablosuyla zamansal tablo oluşturmak, adlandırmayı denetlemek ve yine de varsayılan yapılandırmayla geçmiş tablosunu oluşturmak için sisteme güvenmek istediğinizde kullanışlı bir seçenektir. Aşağıdaki örnek, sistem sürümü etkinleştirilmiş yeni bir tablo oluşturur ve geçmiş tablosunun adı açıkça tanımlanmıştır.

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

Geçmiş tablosu, özellikle adlandırılmış geçmiş tablosu için geçerli olan aşağıdaki kurallarla birlikte "anonim" bir geçmiş tablosu oluşturmak için uygulanan kurallar kullanılarak oluşturulur.

  • şema adı, HISTORY_TABLE parametresi için zorunludur.

  • Belirtilen şema yoksa, CREATE TABLE deyimi başarısız olur.

  • HISTORY_TABLE parametresi tarafından belirtilen tablo zaten varsa, şema tutarlılığı vezamansal veri tutarlılığı açısından yeni oluşturulan zamansal tabloya göre doğrular. Geçersiz bir geçmiş tablosu belirtirseniz, CREATE TABLE deyimi başarısız olur.

Kullanıcı tanımlı geçmiş tablosuyla zamansal tablo oluşturma

Kullanıcı tarafından tanımlanan bir tarih tablosuyla zamansal bir tablo oluşturmak, belirli depolama seçenekleri ve farklı indeksler ile tarihsel sorgulara ayarlanmış bir tarih tablosu belirtmek istediğinizde pratik bir seçenektir. Aşağıdaki örnekte, zaman tablosuyla hizalanmış bir şema ile kullanıcı tanımlı bir geçmiş tablosu oluşturursunuz. Bu geçmiş tablosu, tekil aramalar için kümelenmiş bir sütun deposu dizinine ve ek bir kümelenmemiş satır deposu (B ağacı) dizinine sahiptir. Geçmiş tablosunu oluşturduktan sonra, zamansal tabloyu oluşturur ve kullanıcı tanımlı geçmiş tablosunu varsayılan tarih tablosu olarak belirlersiniz.

Note

Belgelerde genellikle dizinlere başvuruda B ağacı terimi kullanılır. Rowstore dizinlerinde Veritabanı Altyapısı bir B+ ağacı uygular. Bu, sütun deposu dizinleri veya bellek için iyileştirilmiş tablolardaki dizinler için geçerli değildir. Daha fazla bilgi için SQL Server ve Azure SQL dizin mimarisi ve tasarım kılavuzuna bakın.

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

Eğer toplama veya pencere fonksiyonları kullanan geçmiş verilerde analitik sorgular yapmayı planlıyorsanız, sıkıştırma ve sorgu performansı için birincil indeks olarak kümelenmiş bir columnstore oluşturmak şiddetle tavsiye edilir.

Veri denetimi için zamansal tablolar kullanmayı planlıyorsanız (geçerli tablodan tek bir satır için geçmiş değişiklikleri arama), kümelenmiş dizine sahip bir satır deposu geçmiş tablosu oluşturmanız gerekir.

Geçmiş tablosunda birincil anahtar, yabancı anahtarlar, benzersiz dizinler, tablo kısıtlamaları veya tetikleyiciler olamaz. Değişiklik verileri yakalama, değişiklik izleme, işlem çoğaltması veya birleştirme çoğaltması için yapılandırılamaz.

Fabric SQL veritabanında ve Fabric yansıtmanın yapılandırıldığı Azure SQL Veritabanı'nda, zamansal tablo oluşturma sırasında geçmiş tablosu olarak mevcut bir tabloyu kullandığınızda, mevcut tablo yansıtılmayı durdurur.

Zamansal olmayan tabloyu sistem sürümüne sahip bir zamana bağlı tablo olacak şekilde değiştirme

Mevcut zamansal olmayan bir tabloda sistem sürümü etkinleştirebilirsiniz, örneğin özel zamansal çözümü yerleşik desteğe taşımak istediğinizde.

Örneğin, sürüm oluşturmanın tetikleyicilerle uygulandığı bir tablo kümeniz olabilir. Zamansal sistem sürümü oluşturmanın kullanılması daha az karmaşıktır ve aşağıdakiler gibi başka avantajlar sağlar:

  • Sabit geçmiş
  • Zaman yolculuğu sorguları için yeni söz dizimi
  • Daha iyi DML performansı
  • En düşük bakım maliyetleri

Mevcut bir tabloyu dönüştürürken, sütun adlarını açıkça belirtmeyen (örneğin, sütun listesi olmadan SELECT * veya INSERT) ve yeni sütunları işleyecek şekilde tasarlanmamış mevcut uygulamaları etkilememek için, yeni ValidFrom sütunlarını (ValidTo sütunları PERIOD ve HIDDEN) gizlemek üzere ifadesini kullanmayı düşünün.

Zamana bağlı olmayan tablolara sürüm oluşturma ekleme

Verileri içeren zamansal olmayan bir tablo için değişiklikleri izlemeye başlamak istiyorsanız, PERIOD tanımını eklemeniz ve isteğe bağlı olarak SQL Server'ın sizin için oluşturduğu boş geçmiş tablosu için bir ad sağlamanız gerekir:

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

DATETIME2 hassasiyeti, temel alınan tablonun hassasiyetiyle uyumlu olmalıdır.

Remarks

Var olan bir tabloya veri içeren varsayılan değer atanamayan sütunlar eklemek, SQL Server Enterprise sürümü dışındaki tüm sürümlerde (meta veri işlemidir) veri işleminin boyutudur. SQL Server Standard sürümünde veri içeren büyük bir geçmiş tablosuyla null olmayan bir sütun eklemek pahalı bir işlem olabilir.

Dönem başlangıç ve dönem bitiş sütunları için kısıtlamalar dikkatle seçilmelidir:

  • Başlangıç sütunu için varsayılan değer, var olan satırların geçerli olduğunu düşündüğünüz zamanı belirtir. Gelecekte bir tarih ve saat noktası olarak belirtilemez.

  • Bitiş saati, belirli bir datetime2 duyarlığı için en yüksek değer olarak belirtilmelidir, örneğin 9999-12-31 23:59:59 veya 9999-12-31 23:59:59.9999999.

PERIOD eklemek, mevcut dönem sütunlarındaki değerlerin geçerli olduğundan emin olmak için geçerli tablo üzerinde bir veri tutarlılığı denetimi gerçekleştirir.

SYSTEM_VERSIONINGetkinleştirilirken mevcut bir geçmiş tablosu belirtildiğinde, hem geçerli hem de geçmiş tablosunda bir veri tutarlılığı denetimi gerçekleştirilir. ek parametre olarak DATA_CONSISTENCY_CHECK = OFF belirtirseniz atlanabilir.

Mevcut tabloları yerleşik desteğe geçirme

Bu örnek, tetikleyicileri temel alarak mevcut bir çözümden yerleşik zamana bağlı desteğe nasıl geçiş yapılacağını gösterir. Bu örnek, mevcut özel çözümün mevcut ve geçmiş verileri iki ayrı kullanıcı tablosuna (ProjectTaskCurrent ve ProjectTaskHistory) ayırdığını varsaymaktadır.

Mevcut çözümünüz gerçek ve geçmiş satırları depolamak için tek bir tablo kullanıyorsa, aşağıdaki örnekte gösterilen göç adımlarından önce verileri iki tabloya bölmelisiniz. İlk olarak, tetikleyiciyi gelecekteki zamansal tablodan kaldırın. Ardından, PERIOD sütunlarının null değer atanamaz olduğundan emin olun.

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

PERIOD tanımında var olan sütunlara başvurmak, generated_always_type örtük olarak bu sütunlar için AS_ROW_START ve AS_ROW_END olarak değişir.

PERIOD eklemek, dönem sütunlarındaki mevcut değerlerin geçerli olduğundan emin olmak için mevcut tabloda veri tutarlılığı denetimi gerçekleştirir.

Mevcut veriler üzerinde veri tutarlılığı denetimlerini zorunlu kılmak için SYSTEM_VERSIONINGile DATA_CONSISTENCY_CHECK = ON ayarlamanızı kesinlikle öneririz.

Gizli sütunlar tercih ediliyorsa, aşağıdaki komutu kullanın:

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

İlgili içerik

  • Last updated on