TechCodeview

IDENTITY anahtar sözcüğü SQL Server'daki bir özelliktir. Bir tablo sütunu bir kimlik özelliğiyle tanımlandığında değeri otomatik olarak oluşturulan artımlı değer olacaktır. . Bu değer sunucu tarafından otomatik olarak oluşturulur. Bu nedenle kullanıcı olarak kimlik sütununa manuel olarak değer giremiyoruz. Bu nedenle, bir sütunu kimlik olarak işaretlersek, SQL Server onu otomatik olarak artan bir şekilde dolduracaktır.

Sözdizimi

SQL Server'da IDENTITY özelliğinin kullanımını gösteren söz dizimi aşağıdadır:

 IDENTITY[(seed, increment)] 

Yukarıdaki sözdizimi parametreleri aşağıda açıklanmıştır:

Tohum:Tabloya yüklenen satırın başlangıç ​​değerini belirtir. Varsayılan olarak değeri 1'dir.Artış:Son yüklenen satırın kimlik değerine eklenen artımlı değeri gösterir. Varsayılan olarak değeri 1'dir.

Bu kavramı basit bir örnekle anlayalım.

Diyelim ki elimizde bir ' Öğrenci ' masa ve biz istiyoruz Öğrenci Kimliği otomatik olarak oluşturulacaktır. bizde bir başlangıç ​​öğrenci kimliği 10 ve her yeni kimlikle bunu 1 artırmak istiyorum. Bu senaryoda aşağıdaki değerlerin tanımlanması gerekir.

Tohum: 10

Artış: 1

 CREATE TABLE Student ( StudentID INT IDENTITY(10, 1) PRIMARY KEY NOT NULL, ) 

NOT: SQL Server'da tablo başına yalnızca bir tanımlama sütununa izin verilir.

SQL Server KİMLİK Örneği

Tabloda kimlik özelliğini nasıl kullanabileceğimizi anlayalım. Bir sütundaki kimlik özelliği, yeni tablo oluşturulduğunda veya oluşturulduktan sonra ayarlanabilir. Burada her iki durumu da örneklerle göreceğiz.

Yeni tabloyla IDENTITY özelliği

Aşağıdaki ifade, belirtilen veritabanında kimlik özelliğine sahip yeni bir tablo oluşturacaktır:

 CREATE TABLE person ( PersonID INT IDENTITY(10,1) PRIMARY KEY NOT NULL, Fullname VARCHAR(100) NOT NULL, Occupation VARCHAR(50), Gender VARCHAR(10) NOT NULL ); 

Daha sonra bu tabloya yeni bir satır ekleyeceğiz. ÇIKTI Otomatik olarak oluşturulan kişi kimliğini görmek için madde:

 INSERT INTO person(Fullname, Occupation, Gender) OUTPUT inserted.PersonID VALUES('Sara Jackson', 'HR', 'Female'); 

Bu sorguyu yürütmek aşağıdaki çıktıyı gösterecektir:

Bu çıktı, ilk satırın on değeriyle eklendiğini gösterir. Kişi Kimliği tablo tanımı kimlik sütununda belirtildiği gibi sütun.

içine bir satır daha ekleyelim kişi masası aşağıda olduğu gibi:

 INSERT INTO person(Fullname, Occupation, Gender) OUTPUT inserted.* VALUES('Mark Boucher', 'Cricketer', 'Male'), ('Josh Phillip', 'Writer', 'Male'); 

Bu sorgu aşağıdaki çıktıyı döndürecektir:

Bu çıktı, PersonID sütununda ikinci satırın 11 değeriyle, üçüncü satırın ise 12 değeriyle eklendiğini gösterir.

Mevcut tabloyla IDENTITY özelliği

Bu kavramı öncelikle yukarıdaki tabloyu silerek ve bunları kimlik özelliği olmadan oluşturarak açıklayacağız. Tabloyu bırakmak için aşağıdaki ifadeyi yürütün:

 DROP TABLE person; 

Daha sonra aşağıdaki sorguyu kullanarak bir tablo oluşturacağız:

 CREATE TABLE person ( Fullname VARCHAR(100) NOT NULL, Occupation VARCHAR(50), Gender VARCHAR(10) NOT NULL ); 

Mevcut bir tabloya,identity özelliğine sahip yeni bir sütun eklemek istersek ALTER komutunu kullanmamız gerekir. Aşağıdaki sorgu, PersonID'yi kişi tablosuna bir kimlik sütunu olarak ekleyecektir:

 ALTER TABLE person ADD PersonID INT IDENTITY(10,1) PRIMARY KEY NOT NULL; 

Kimlik sütununa açıkça değer ekleme

Yukarıdaki tabloya kimlik sütun değerini açıkça belirterek yeni bir satır eklersek SQL Server hata atacaktır. Aşağıdaki sorguya bakın:

 INSERT INTO person(Fullname, Occupation, Gender, PersonID) VALUES('Mary Smith', 'Business Analyst', 'Female', 13); 

Bu sorguyu yürütmek aşağıdaki hatayı verecektir:

Kimlik sütunu değerini açıkça eklemek için öncelikle IDENTITY_INSERT değerini AÇIK olarak ayarlamamız gerekir. Daha sonra tabloya yeni bir satır eklemek için ekleme işlemini gerçekleştirin ve ardından IDENTITY_INSERT değerini OFF olarak ayarlayın. Aşağıdaki kod komut dosyasına bakın:

 SET IDENTITY_INSERT person ON /*INSERT VALUE*/ INSERT INTO person(Fullname, Occupation, Gender, PersonID) VALUES('Mary Smith', 'Business Analyst', 'Female', 14); SET IDENTITY_INSERT person OFF SELECT * FROM person; 

IDENTITY_INSERT ON kullanıcıların kimlik sütunlarına veri koymasına olanak tanırken IDENTITY_INSERT KAPALI bu sütuna değer katmalarını engeller.

Kod betiğini çalıştırmak, 14 değerine sahip PersonID'nin başarıyla eklendiğini görebildiğimiz aşağıdaki çıktıyı görüntüleyecektir.

KİMLİK İşlevi

SQL Server, bir tablodaki IDENTITY sütunlarıyla çalışmak için bazı kimlik işlevleri sağlar. Bu kimlik işlevleri aşağıda listelenmiştir:

1. @@IDENTITY İşlevi
2. SCOPE_IDENTITY() İşlevi
3. IDENT_CURRENT İşlev
4. KİMLİK İşlevi

IDENTITY fonksiyonlarına bazı örneklerle bakalım.

@@IDENTITY İşlevi

@@IDENTITY, sistem tanımlı bir işlevdir ve son kimlik değerini görüntüler Aynı oturumda IDENTITY sütunu için bir tabloda oluşturulan (kullanılan maksimum kimlik değeri). Bu işlev sütunu, tabloya yeni bir giriş eklendikten sonra ifade tarafından oluşturulan kimlik değerini döndürür. Bir döndürür HÜKÜMSÜZ IDENTITY değeri oluşturmayan bir sorgu çalıştırdığımızda değer. Her zaman geçerli oturumun kapsamında çalışır. Uzaktan kullanılamaz.

Örnek

Kişi tablosundaki mevcut maksimum kimlik değerinin 13 olduğunu varsayalım. Şimdi aynı oturumda kimlik değerini birer artıran bir kayıt ekleyeceğiz. Daha sonra aynı oturumda oluşturulan son kimlik değerini almak için @@IDENTITY fonksiyonunu kullanacağız.

İşte tam kod komut dosyası:

 SELECT MAX(PersonID) AS maxidentity FROM person; INSERT INTO person(Fullname, Occupation, Gender) VALUES('Brian Lara', 'Cricket', 'Male'); SELECT @@IDENTITY; 

Komut dosyasının çalıştırılması, kullanılan maksimum kimlik değerinin 14 olduğunu görebildiğimiz aşağıdaki çıktıyı döndürecektir.

SCOPE_IDENTITY() İşlevi

SCOPE_IDENTITY() sistem tanımlı bir fonksiyondur. en son kimlik değerini göster geçerli kapsamın altındaki bir tabloda. Bu kapsam bir modül, tetikleyici, işlev veya saklı prosedür olabilir. Bu işlevin yalnızca sınırlı bir kapsama sahip olması dışında @@IDENTITY() işlevine benzer. SCOPE_IDENTITY işlevi, aynı kapsamda bir değer üreten ekleme işleminden önce çalıştırırsak NULL değerini döndürür.

Örnek

Aşağıdaki kod aynı oturumda hem @@IDENTITY hem de SCOPE_IDENTITY() işlevini kullanır. Bu örnekte ilk önce son kimlik değeri görüntülenecek, ardından tabloya bir satır eklenecektir. Daha sonra her iki kimlik işlevini de yürütür.

 SELECT MAX(PersonID) AS maxid FROM person; INSERT INTO person(Fullname, Occupation, Gender) VALUES('Jennifer Winset', 'Actoress', 'Female'); SELECT SCOPE_IDENTITY(); SELECT @@IDENTITY; 

Kodun çalıştırılması, geçerli oturumda ve benzer kapsamda aynı değeri gösterecektir. Aşağıdaki çıktı resmine bakın:

Şimdi bir örnekle her iki fonksiyonun ne kadar farklı olduğunu göreceğiz. Öncelikle adında iki tablo oluşturacağız. çalışan_verileri Ve departman aşağıdaki ifadeyi kullanarak:

 CREATE TABLE employee_data ( emp_id INT IDENTITY(1, 1) PRIMARY KEY NOT NULL, fullname VARCHAR(20) NULL ) GO CREATE TABLE department ( department_id INT IDENTITY(100, 5) PRIMARY KEY, department_name VARCHAR(20) NULL ); 

Daha sonra, Employee_data tablosunda bir INSERT tetikleyicisi oluşturuyoruz. Bu tetikleyici, çalışan_verileri tablosuna bir satır eklediğimizde departman tablosuna bir satır eklemek için çağrılır.

Aşağıdaki sorgu, varsayılan değerin eklenmesi için bir tetikleyici oluşturur 'BT' departman tablosunda, çalışan_verisi tablosundaki her ekleme sorgusunda:

pd.merge

 CREATE TRIGGER Insert_Department ON employee_data FOR INSERT AS BEGIN INSERT INTO department VALUES ('IT') END; 

Trigger oluşturduktan sonra, Employee_data tablosuna bir kayıt ekleyeceğiz ve hem @@IDENTITY hem de SCOPE_IDENTITY() fonksiyonlarının çıktısını göreceğiz.

 INSERT INTO employee_data VALUES ('John Mathew'); 

Sorgunun yürütülmesi, çalışan_verileri tablosuna bir satır ekleyecek ve aynı oturumda bir kimlik değeri oluşturacaktır. Ekleme sorgusu, çalışan_verileri tablosunda yürütüldüğünde, departman tablosuna bir satır eklemek için otomatik olarak bir tetikleyici çağırır. Kimlik tohum değeri, Employee_data için 1 ve departman tablosu için 100'dür.

Son olarak, yalnızca aynı kapsamda kimlik değeri döndürdükleri için SELECT @@IDENTITY işlevi için 100 ve SCOPE_IDENTITY işlevi için 1 çıktısını görüntüleyen aşağıdaki ifadeleri çalıştırıyoruz.

 SELECT MAX(emp_id) FROM employee_data SELECT MAX(department_id) FROM department SELECT @@IDENTITY SELECT SCOPE_IDENTITY() 

İşte sonuç:

IDENT_CURRENT() İşlev

IDENT_CURRENT, sistem tanımlı bir işlevdir. en güncel IDENTITY değerini göster herhangi bir bağlantı altında belirli bir tablo için oluşturulur. Bu işlev, kimlik değerini oluşturan SQL sorgusunun kapsamını dikkate almaz. Bu fonksiyon kimlik değerini almak istediğimiz tablonun adını gerektirir.

Örnek

Bunu öncelikle iki bağlantı penceresini açarak anlayabiliriz. Kişi tablosunda 15 kimlik değerini oluşturan ilk pencereye bir kayıt ekleyeceğiz. Daha sonra aynı çıktıyı görebileceğimiz başka bir bağlantı penceresinde bu kimlik değerini doğrulayabiliriz. İşte kodun tamamı:

 1st Connection Window INSERT INTO person(Fullname, Occupation, Gender) VALUES('John Doe', 'Engineer', 'Male'); GO SELECT MAX(PersonID) AS maxid FROM person; 2nd Connection Window SELECT MAX(PersonID) AS maxid FROM person; GO SELECT IDENT_CURRENT('person') AS identity_value; 

Yukarıdaki kodları iki farklı pencerede çalıştırmak aynı kimlik değerini gösterecektir.

KİMLİK() İşlevi

IDENTITY() işlevi sistem tanımlı bir işlevdir yeni bir tabloya kimlik sütunu eklemek için kullanılır . Bu fonksiyon, CREATE TABLE ve ALTER TABLE ifadeleriyle kullandığımız IDENTITY özelliğinden farklıdır. Bu fonksiyonu yalnızca bir tablodan diğerine veri aktarırken kullanılan SELECT INTO deyiminde kullanabiliriz.

Aşağıdaki sözdizimi bu işlevin SQL Server'da kullanımını gösterir:

 IDENTITY (data_type , seed , increment) AS column_name 

Kaynak tablonun IDENTITY sütunu varsa, SELECT INTO komutuyla oluşturulan tablo varsayılan olarak bu sütunu devralır. Örneğin , daha önce kimlik sütununa sahip bir tablo kişisi oluşturduk. IDENTITY() işleviyle birlikte SELECT INTO ifadelerini kullanarak kişi tablosunu miras alan yeni bir tablo oluşturduğumuzu varsayalım. Bu durumda kaynak tabloda zaten bir kimlik sütunu bulunduğundan hata alacağız. Aşağıdaki sorguya bakın:

 SELECT IDENTITY(INT, 100, 2) AS NEW_ID, PersonID, Fullname, Occupation, Gender INTO person_info FROM person; 

Yukarıdaki ifadenin yürütülmesi aşağıdaki hata mesajını döndürecektir:

Aşağıdaki ifadeyi kullanarak, kimlik özelliği olmayan yeni bir tablo oluşturalım:

 CREATE TABLE student_data ( roll_no INT PRIMARY KEY NOT NULL, fullname VARCHAR(20) NULL ) 

Daha sonra, IDENTITY işlevini içeren SELECT INTO ifadesini kullanarak bu tabloyu aşağıdaki gibi kopyalayın:

 SELECT IDENTITY(INT, 100, 1) AS student_id, roll_no, fullname INTO temp_data FROM student_data; 

İfade yürütüldükten sonra, bunu kullanarak doğrulayabiliriz. sp_help Tablo özelliklerini görüntüleyen komut.

KİMLİK sütununu görebilirsiniz. KENDİNE UYGUN Belirtilen koşullara göre özellikler.

Bu fonksiyonu SELECT deyimiyle kullanırsak SQL Server aşağıdaki hata mesajını verecektir:

Msg 177, Düzey 15, Durum 1, Satır 2 IDENTITY işlevi yalnızca SELECT ifadesinin INTO yan tümcesi olduğunda kullanılabilir.

IDENTITY değerlerini yeniden kullanma

SQL Server tablosundaki kimlik değerlerini yeniden kullanamayız. Kimlik sütunu tablosundan herhangi bir satırı sildiğimizde kimlik sütununda boşluk oluşacaktır. Ayrıca SQL Server, kimlik sütununa yeni bir satır eklediğimizde ve ifade başarısız olduğunda veya geri alındığında bir boşluk yaratacaktır. Boşluk, kimlik değerlerinin kaybolduğunu ve KİMLİK sütununda yeniden oluşturulamayacağını gösterir.

Pratik olarak anlamak için aşağıdaki örneği inceleyin. Aşağıdaki verileri içeren bir kişi tablomuz zaten var:

Daha sonra adında iki tablo daha oluşturacağız. 'konum' , Ve ' kişi_pozisyon ' aşağıdaki ifadeyi kullanarak:

 CREATE TABLE POSITION ( PositionID INT IDENTITY (1, 1) PRIMARY KEY, Position_name VARCHAR (255) NOT NULL ); CREATE TABLE person_position ( PersonID INT, PositionID INT, PRIMARY KEY (PersonID, PositionID), FOREIGN KEY (PersonID) REFERENCES person (PersonID), FOREIGN KEY (PositionID) REFERENCES POSITION (PositionID) ); 

Daha sonra, kişi tablosuna yeni bir kayıt ekleyip, Person_position tablosuna yeni bir satır ekleyerek bu kayıtlara konum atamaya çalışıyoruz. Bunu aşağıdaki gibi işlem bildirimini kullanarak yapacağız:

 BEGIN TRANSACTION BEGIN TRY -- insert a new row into the person table INSERT INTO person (Fullname, Occupation, Gender) VALUES('Joan Smith', 'Manager', 'Male'); -- assign a position to a new person INSERT INTO person_position (PersonID, PositionID) VALUES(@@IDENTITY, 10); END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH IF @@TRANCOUNT > 0 COMMIT TRANSACTION; 

Yukarıdaki işlem kodu komut dosyası, ilk ekleme ifadesini başarıyla yürütür. Ancak ikinci ifade, konum tablosunda on numaralı konum olmadığından başarısız oldu. Bu nedenle tüm işlem geri alındı.

PersonID sütununda maksimum kimlik değerimiz 16 olduğundan, ilk ekleme ifadesi 17 kimlik değerini tüketti ve ardından işlem geri alındı. Dolayısıyla Person tablosuna bir sonraki satırı eklersek bir sonraki kimlik değeri 18 olacaktır. Aşağıdaki ifadeyi yürütün:

 INSERT INTO person(Fullname, Occupation, Gender) VALUES('Peter Drucker',' Writer', 'Female'); 

Kişi tablosunu tekrar kontrol ettiğimizde yeni eklenen kaydın 18 kimlik değerini içerdiğini görüyoruz.

Tek bir tabloda iki IDENTITY sütunu

Teknik olarak tek bir tabloda iki kimlik sütunu oluşturmak mümkün değildir. Bunu yaparsak SQL Server hata atar. Aşağıdaki sorguya bakın:

 CREATE TABLE TwoIdentityTable ( ID1 INT IDENTITY (10, 1) NOT NULL, ID2 INT IDENTITY (100, 1) NOT NULL ) 

Bu kodu çalıştırdığımızda aşağıdaki hatayı göreceğiz:

Ancak hesaplanan sütunu kullanarak tek bir tabloda iki kimlik sütunu oluşturabiliriz. Aşağıdaki sorgu, orijinal kimlik sütununu kullanan ve onu 1 azaltan hesaplanmış bir sütuna sahip bir tablo oluşturur.

 CREATE TABLE TwoIdentityTable ( ID1 INT IDENTITY (10, 1) NOT NULL, SecondID AS 10000-ID1, Descriptions VARCHAR(60) ) 

Daha sonra aşağıdaki komutu kullanarak bu tabloya bazı veriler ekleyeceğiz:

 INSERT INTO TwoIdentityTable (Descriptions) VALUES ('Javatpoint provides best educational tutorials'), ('www.javatpoint.com') 

Son olarak SELECT ifadesini kullanarak tablo verilerini kontrol ediyoruz. Aşağıdaki çıktıyı döndürür:

Resimde SecondID sütununun 9990 başlangıç ​​değerinden on birim azalarak ikinci kimlik sütunu gibi davrandığını görebiliriz.

SQL Server'ın IDENTITY sütunu yanılgıları

DBA kullanıcısının SQL Server kimlik sütunlarıyla ilgili birçok yanılgısı vardır. Kimlik sütunlarıyla ilgili en yaygın görülen yanılgıların listesi aşağıda verilmiştir:

KİMLİK sütunu EŞSİZdir: SQL Server'ın resmi belgelerine göre, kimlik özelliği sütun değerinin benzersiz olduğunu garanti edemez. Sütun benzersizliğini uygulamak için PRIMARY KEY, UNIQUE kısıtlaması veya UNIQUE dizini kullanmalıyız.

KİMLİK sütunu ardışık sayılar üretir: Resmi belgeler, kimlik sütununda atanan değerlerin bir veritabanı arızası veya sunucunun yeniden başlatılması durumunda kaybolabileceğini açıkça belirtir. Ekleme sırasında kimlik değerinde boşluklara neden olabilir. Boşluk, değeri tablodan sildiğimizde veya insert ifadesi geri alındığında da oluşturulabilir. Boşluk oluşturan değerler daha fazla kullanılamaz.

IDENTITY sütunu mevcut değerleri otomatik olarak oluşturamaz: Kimlik özelliği DBCC CHECKIDENT komutu kullanılarak yeniden sağlanana kadar kimlik sütununun mevcut değerleri otomatik olarak oluşturması mümkün değildir. Identity özelliğinin tohum değerini (satırın başlangıç ​​değeri) ayarlamamızı sağlar. Bu komutu uyguladıktan sonra SQL Server, tabloda zaten mevcut olan yeni oluşturulan değerleri kontrol etmeyecektir.

PRIMARY KEY olarak IDENTITY sütunu, satırı tanımlamak için yeterlidir: Birincil anahtar, tablodaki kimlik sütununu başka benzersiz kısıtlamalar olmadan içeriyorsa, sütun yinelenen değerleri saklayabilir ve sütun benzersizliğini önleyebilir. Bildiğimiz gibi, birincil anahtar yinelenen değerleri depolayamaz, ancak kimlik sütunu yinelenen değerleri depolayabilir; birincil anahtar ve kimlik özelliğinin aynı sütunda kullanılmaması önerilir.

Eklemeden sonra kimlik değerlerini geri almak için yanlış aracı kullanmak: Az önce yürüttüğümüz ifadeden doğrudan eklenen kimlik değerini almak için @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT ve IDENTITY() işlevleri arasındaki farkların farkında olunmaması da yaygın bir yanılgıdır.

DİZİ ve KİMLİK arasındaki fark

Otomatik numaralar oluşturmak için hem SEQUENCE hem de IDENTITY'yi kullanırız. Bununla birlikte, bazı farklılıkları vardır ve temel fark, kimliğin tabloya bağlı olması, ancak sıranın olmamasıdır. Farklılıklarını tablo halinde özetleyelim:

Çözüm

Bu makale, SQL Server'daki IDENTITY özelliğine tam bir genel bakış sunacaktır. Burada kimlik özelliğinin nasıl ve ne zaman kullanıldığını, farklı işlevlerini, kavram yanılgılarını, diziden nasıl farklı olduğunu öğrendik.