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 DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Geçmiş tablosunun nasıl belirtildiğini göz önünde bulundurarak sistem sürümüne sahip bir zamana bağlı tablo oluşturmanın üç yolu vardır:

• anonim geçmiş tablosuile zamansal tablo: geçerli tablonun şemasını belirlersiniz ve sistemin otomatik olarak oluşturulan ada sahip ilişkili 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. SYSTEM_VERSIONING yan tümcesinde herhangi bir parametre gerektirmediğinden, geçici tablo oluşturmanın da en basit yoludur. Aşağıdaki örnekte, sistem sürümü etkinleştirilmiş ve geçmiş tablosunun adı tanımlanmadan oluşturulmuş yeni bir tablo oluşturulur.

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.

Anonim geçmiş tablosu, geçerli veya zamana bağlı tabloyla aynı şemada otomatik olarak oluşturulur.

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 olarak oluşturulan bir ada sahip varsayılan 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 örnekte, sistem sürümü etkinleştirilmiş ve geçmiş tablosunun belirli bir adıyla yeni bir tablo oluşturulur.

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ı tanımlı geçmiş tablosuyla zamansal tablo oluşturmak, kullanıcı belirli depolama seçeneklerine ve geçmiş sorgulara ayarlanmış farklı dizinlere sahip bir geçmiş tablosu belirtmek istediğinde kullanışlı bir seçenektir. Aşağıdaki örnekte, kullanıcı tanımlı geçmiş tablosu, oluşturulan zamana bağlı tabloyla hizalanmış bir şemayla oluşturulur. Bu kullanıcı tanımlı geçmiş tablosunda, nokta aramaları için kümelenmiş bir columnstore dizini ve fazladan bir kümelenmemiş satır deposu (B-tree) dizini oluşturulur. Bu kullanıcı tanımlı geçmiş tablosu oluşturulduktan sonra, kullanıcı tanımlı geçmiş tablosu varsayılan geçmiş tablosu olarak belirterek zamansal tablo oluşturulur.

Note

Belgelerde genellikle dizinlere başvuruda B ağacı terimi kullanılır. Rowstore dizinlerinde Veritabanı Altyapısı bir B+ ağacı uygular. Bu, columnstore dizinleri veya bellek 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

Toplamaları veya pencere işlevlerini kullanan geçmiş veriler üzerinde analiz sorguları çalıştırmayı planlıyorsanız, sıkıştırma ve sorgu performansı için birincil dizin olarak kümelenmiş bir columnstore oluşturulması kesinlikle önerilir.

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

Özel bir zamansal çözümü yerleşik desteğe geçirmek istediğinizde olduğu gibi, mevcut bir zamansal olmayan tabloda sistem sürümü oluşturmayı etkinleştirebilirsiniz.

Ö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

Var olan bir tabloyu dönüştürürken, sütun adlarını açıkça belirtmeyen mevcut uygulamaları etkilememek için yeni HIDDEN sütunlarını (PERIOD sütunları ve ValidFrom) gizlemek amacıyla ValidTo yan tümcesini kullanmayı düşünün. Örneğin, sütun listesi olmayan SELECT * veya INSERT yeni sütunları işleyecek şekilde tasarlanmamıştır.

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, dönem sütunları için mevcut değerlerin geçerli olduğundan emin olmak için geçerli tabloda 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 örnekte, geçerli özel çözümün geçerli ve geçmiş verileri iki ayrı kullanıcı tablosuna (ProjectTaskCurrent ve ProjectTaskHistory) böldüğünü varsayıyoruz.

Mevcut çözümünüz gerçek ve geçmiş satırları depolamak için tek tablo kullanıyorsa, aşağıdaki örnekte gösterilen geçiş adımlarını izlemeden önce verileri iki tabloya bölmeniz gerekir. İ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 that 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ı için mevcut değerlerin geçerli olduğundan emin olmak için geçerli tabloda bir 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 edilirse ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;komutunu kullanın.

İlgili içerik

• Zamansal tablolar
• Sistem sürümüne sahip zamana bağlı tabloları kullanmaya başlama
• Sistem sürümündeki zamana bağlı tablolarda geçmiş verileri saklamayı yönetme
• Sistem sürümlü zamana dayalı tablolar ile bellek için optimize edilmiş tablolar
• TABLO OLUŞTUR (Transact-SQL)
• Sistem sürümüne sahip bir zamana bağlı tablodaki verileri değiştirme
• Sistem sürümüne sahip bir zamana bağlı tablodaki verileri sorgulama
• Sistem sürümüne sahip bir zamana bağlı tablonun şemasını değiştirme
• Sistem sürümüne sahip bir zamana bağlı tabloda sistem sürümü oluşturmayı durdurma