Aracılığıyla paylaş

Ayrılmış SQL havuzunda vekil anahtarlar oluşturmak için KIMLIK kullanma

Bu makalede, ayrılmış SQL havuzundaki IDENTITY tablolarda vekil anahtarlar oluşturmak için özelliğini kullanmaya yönelik öneriler ve örnekler bulacaksınız.

Surrogate key (vekil anahtar) nedir?

Tablodaki yedek anahtar, her satır için benzersiz bir tanımlayıcı içeren sütundur. Anahtar tablo verilerinden oluşturulmaz. Veri modelleyicileri, veri ambarı modelleri tasarlarken tablolarında vekil anahtarlar oluşturmayı sever. Bu hedefe ulaşmak için özelliğini yük performansını etkilemeden basit ve etkili bir şekilde kullanabilirsiniz IDENTITY .

Not

Azure Synapse Analytics'te:

  • IDENTITY değeri her dağıtımda kendi kendine artar ve diğer dağıtımlardaki IDENTITY değerleriyle çakışmaz. Synapse'teki IDENTITY değeri, kullanıcı SET IDENTITY_INSERT ON ile açıkça yinelenen bir değer eklediğinde veya IDENTITY'yi yeniden başlattığında benzersiz olacağı garanti edilmemektedir. Ayrıntılar için bkz. CREATE TABLE (Transact-SQL) IDENTITY (Özellik).
  • Dağıtım sütunundaKI UPDATE, IDENTITY değerinin benzersiz olduğunu garanti etmez. Benzersizliği doğrulamak için dağıtım sütununda UPDATE sonrasında DBCC CHECKIDENT (Transact-SQL) kullanın.

IDENTITY sütunuyla tablo oluşturma

IDENTITY özelliği, yük performansını etkilemeden ayrılmış SQL havuzundaki tüm dağıtımların ölçeğini genişletecek şekilde tasarlanmıştır. Bu nedenle IDENTITY'in uygulaması bu hedeflere ulaşmaya yöneliktir.

Tabloyu ilk kez oluşturduğunuzda, aşağıdaki cümleye benzer bir söz dizimi kullanarak IDENTITY özelliğine sahip olacak şekilde tanımlayabilirsiniz.

CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1) NOT NULL,
     C2 INT NULL
)
WITH
(   DISTRIBUTION = HASH(C2),
    CLUSTERED COLUMNSTORE INDEX
);

Daha sonra tabloyu doldurmak için kullanabilirsiniz INSERT..SELECT .

Bu bölümün geri kalanında, bunları daha iyi anlamanıza yardımcı olmak için uygulamanın nüansları vurgulanır.

Değerleri ayırma

özelliği, IDENTITY veri ambarının dağıtılmış mimarisi nedeniyle vekil değerlerin ayrılma sırasını garanti etmez. IDENTITY özelliği, yük performansını etkilemeden ayrılmış SQL havuzundaki tüm dağıtımların ölçeğini genişletecek şekilde tasarlanmıştır.

Aşağıdaki örnek bir çizimdir:

CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1) NOT NULL,
     C2 VARCHAR(30) NULL
)
WITH
(   DISTRIBUTION = HASH(C2),
    CLUSTERED COLUMNSTORE INDEX
);

INSERT INTO dbo.T1
VALUES (NULL);

INSERT INTO dbo.T1
VALUES (NULL);

SELECT *
FROM dbo.T1;

DBCC PDW_SHOWSPACEUSED('dbo.T1');

Önceki örnekte, iki satır dağıtım 1'de yer aldı. İlk satır sütununda C11 vekil değerine, ikinci satır ise 61 vekil değerine sahiptir. Bu değerlerin her ikisi de özelliği tarafından IDENTITY oluşturuldu. Ancak, değerlerin ayrılması bitişik değildir. Bu davranış, tasarım gereğidir.

Çarpık veriler

Veri türünün değer aralığı dağıtımlar arasında eşit olarak yayılır. Dağıtılmış bir tabloda sapmış veriler bulunuyorsa, veri türünün kullanılabilir değer aralığı erken tükenebilir. Örneğin, tüm veriler tek bir dağıtımla sonuçlanırsa, tablonun veri türünün yalnızca altıda birine erişimi olur. Bu nedenle özelliği IDENTITY yalnızca ve INT veri türleriyle BIGINT sınırlıdır.

SELECT..INTO

Mevcut IDENTITY sütun yeni bir tabloya seçildiğinde, aşağıdaki koşullardan biri geçerli olmadığı takdirde, yeni sütun IDENTITY özelliğini devralır.

  • Bu SELECT deyimi bir birleşim içerir.
  • Birden çok SELECT deyim UNION kullanılarak birleştirilir.
  • IDENTITY sütunu, SELECT listesinde birden fazla kez listeleniyor.
  • IDENTITY Sütun ifadenin bir parçasıdır.

Eğer bu koşullardan herhangi biri doğruysa, sütun, IDENTITY özelliğini devralmak yerine NOT NULL olarak oluşturulur.

TABLO OLUŞTUR SEÇİM OLARAK

CREATE TABLE AS SELECT (CTAS), için SELECT..INTObelgelenen aynı SQL Server davranışını izler. Ancak, ifadenin CREATE TABLE kısmının sütun tanımında IDENTITY özelliği belirtemezsiniz. IDENTITY işlevini CTAS SELECT bölümünde de kullanamazsınız. Tabloyu doldurmak için önce CREATE TABLE kullanarak tabloyu tanımlamanız, ardından INSERT..SELECT kullanarak doldurmanız gerekir.

Identity sütununa açık değerler ekleme

Tahsis edilmiş SQL havuzu SET IDENTITY_INSERT <your table> ON|OFF sözdizimini destekler. Bu söz dizimini kullanarak sütuna IDENTITY açıkça değer ekleyebilirsiniz.

Birçok veri modelleyici, boyutlarında belirli satırlar için önceden tanımlanmış negatif değerler kullanmayı sever. Örnek olarak -1 veya bilinmeyen üye satırı gösteriliyor.

Bir sonraki betik, SET IDENTITY_INSERT kullanarak bu satırın açıkça nasıl ekleneceğini gösterir.

SET IDENTITY_INSERT dbo.T1 ON;

INSERT INTO dbo.T1
(   C1,
    C2
)
VALUES (-1,'UNKNOWN');

SET IDENTITY_INSERT dbo.T1 OFF;

SELECT     *
FROM    dbo.T1;

Verileri yükleme

özelliğinin varlığının IDENTITY veri yükleme kodunuz üzerinde bazı etkileri vardır. Bu bölümde IDENTITY kullanarak tablolara veri yükleme için bazı temel kalıplar vurgulanır.

Kullanarak IDENTITY, bir tabloya veri yüklemek ve vekil anahtar oluşturmak için tabloyu oluşturun ve ardından INSERT..SELECT veya INSERT..VALUES kullanarak yükleme işlemini gerçekleştirin.

Aşağıdaki örnekte temel desen vurgulanır:

--CREATE TABLE with IDENTITY
CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1),
     C2 VARCHAR(30)
)
WITH
(   DISTRIBUTION = HASH(C2),
    CLUSTERED COLUMNSTORE INDEX
);

--Use INSERT..SELECT to populate the table from an external table
INSERT INTO dbo.T1
(C2)
SELECT     C2
FROM    ext.T1;

SELECT *
FROM   dbo.T1;

DBCC PDW_SHOWSPACEUSED('dbo.T1');

Not

Şu anda IDENTITY sütunu olan bir tabloya veri yüklerken CREATE TABLE AS SELECT kullanmak mümkün değildir.

Verileri yükleme hakkında daha fazla bilgi için Ayrılmış SQL havuzları için Ayıklama, Yükleme ve Dönüştürme (ELT) Tasarımı ve Yükleme en iyi uygulamaları bölümlerine bakın.

Sistem görünümleri

özelliğine IDENTITY katalog görünümünü kullanabilirsiniz.

Veritabanı şemasını daha iyi anlamanıza yardımcı olmak için bu örnekte diğer sistem kataloğu görünümleriyle tümleştirme sys.identity_columns gösterilmektedir:

SELECT  sm.name
,       tb.name
,       co.name
,       CASE WHEN ic.column_id IS NOT NULL
             THEN 1
        ELSE 0
        END AS is_identity
FROM        sys.schemas AS sm
JOIN        sys.tables  AS tb           ON  sm.schema_id = tb.schema_id
JOIN        sys.columns AS co           ON  tb.object_id = co.object_id
LEFT JOIN   sys.identity_columns AS ic  ON  co.object_id = ic.object_id
                                        AND co.column_id = ic.column_id
WHERE   sm.name = 'dbo'
AND     tb.name = 'T1'
;

Sınırlamalar

IDENTITY özelliği kullanılamaz:

  • Sütun veri türü veya değilse INTBIGINT
  • Sütun aynı zamanda dağıtım anahtarı olduğunda
  • Tablo bir dış tablo olduğunda

Aşağıdaki ilgili işlevler ayrılmış SQL havuzunda desteklenmez:

Genel görevler

Sütunlarla IDENTITY çalışırken yaygın görevleri gerçekleştirmek için aşağıdaki örnek kodu kullanabilirsiniz.

C1 sütunu aşağıdaki IDENTITY tüm görevlerde yer alır.

Tablo için en yüksek ayrılan değeri bulma

MAX() Dağıtılmış tablo için ayrılan en yüksek değeri belirlemek için işlevini kullanın:

SELECT MAX(C1)
FROM dbo.T1

IDENTITY özelliği için başlangıç değeri ve artış miktarını bulma

Aşağıdaki sorguyu kullanarak bir tablonun kimlik artışı ve tohum yapılandırma değerlerini bulmak için katalog görünümlerini kullanabilirsiniz:

SELECT  sm.name
,       tb.name
,       co.name
,       ic.seed_value
,       ic.increment_value
FROM        sys.schemas AS sm
JOIN        sys.tables  AS tb           ON  sm.schema_id = tb.schema_id
JOIN        sys.columns AS co           ON  tb.object_id = co.object_id
JOIN        sys.identity_columns AS ic  ON  co.object_id = ic.object_id
                                        AND co.column_id = ic.column_id
WHERE   sm.name = 'dbo'
AND     tb.name = 'T1'
;

İlgili içerik