Aby wyjaśnić pojęcie Identity czyli autoinkrementacji w tabelach utworzymy tabele z kolumną ID zdefiniowaną jako IDENTITY oraz kolumną Name
CREATE TABLE [TESTOWA].[dbo].[IdentityTable1]
(
id INT IDENTITY,
name VARCHAR(20)
)
Commands completed successfully.
Zapisujemy do niej kilka wierszy nie podając wartości ID
INSERT INTO [TESTOWA].[dbo].[IdentityTable1]
(name) VALUES ('ADAM'),('PIOTR'),('ANDRZEJ'),('KRZYSZTOF'),('JERZY'),('MARIUSZ');
GO
(6 rows affected)
SELECT * FROM [TESTOWA].[dbo].[IdentityTable1]
id name ----------- -------------------- 1 ADAM 2 PIOTR 3 ANDRZEJ 4 KRZYSZTOF 5 JERZY 6 MARIUSZ (6 rows affected)
Jak widać w kolumnie ID wartości przyjmują kolejne liczby.
Jeśli byśmy chcieli podać liczbę ID w insercie zwróci nam błąd
INSERT INTO [TESTOWA].[dbo].[IdentityTable1]
(id,name) VALUES (1,'ROBERT');
GO
Msg 544, Level 16, State 1, Line 35 Cannot insert explicit value for identity column in table 'IdentityTable1' when IDENTITY_INSERT is set to OFF.
Jest możliwość zapisu z wartością ID z użyciem opcji IDENTITY_INSERT ale o tym napiszę później.
Tworzymy drugą tabelę z kolumną ID (identity) podając wartość od jakiej ma zacząć liczyć ID (2) oraz co ile ma się zwiększać wartość ID (5).
CREATE TABLE IdentityTable2
(
id INT IDENTITY (2,5),
name VARCHAR(20)
)
Commands completed successfully.
Przenosimy dane z tabeli 1 do 2. Jako, że kolumna ID na ustawione Identity przenosimy tylko dane z kolumny Name
INSERT INTO [TESTOWA].[dbo].[IdentityTable2]
SELECT name from [TESTOWA].[dbo].[IdentityTable1]
GO
(6 rows affected)
SELECT * FROM [TESTOWA].[dbo].[IdentityTable2]
id name ----------- -------------------- 2 ADAM 7 PIOTR 12 ANDRZEJ 17 KRZYSZTOF 22 JERZY 27 MARIUSZ (6 rows affected)
Jak wyżej próba zapisania wiersza ze zdefiniowaną wartością ID będzie zwracało błąd.
Wartość @@IDENTITY zwraca nam ostatnią wartość Identity jaką użyliśmy w ostatnio używanej tabeli. Obecnie pokazuje nam wartość z tabeli IdentityTable2, ale jeśli wykonamy insert do tabeli IdentityTable1, będzie pokazywało wartość odpowiednią dla tej tabeli (6)
SELECT @@IDENTITY as 'IDENTITY'
IDENTITY --------------------------------------- 27 (1 row affected)
INSERT INTO [TESTOWA].[dbo].[IdentityTable1]
(name) VALUES ('ROBERT');
GO
SELECT @@IDENTITY as 'IDENTITY'
IDENTITY --------------------------------------- 7 (1 row affected)
Możemy też wydać zapytanie:
SELECT
IDENT_CURRENT('IdentityTable1') as CurrentIdentTable1,
IDENT_CURRENT('IdentityTable2') as CurrentIdentTable2
CurrentIdentTable1 CurrentIdentTable2 --------------------------------------- --------------------------------------- 6 32 (1 row affected)
SELECT SCOPE_IDENTITY() as 'IDENTITY';
IDENTITY --------------------------------------- 32 (1 row affected)
USE TETSOWA
DBCC CHECKIDENT([IdentityTable2])
Checking identity information: current identity value '32', current column value '32'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Aby zmienić wartość identity na pozycji "startową" 3 wykonujemy polecenie:
DBCC CHECKIDENT([IdentityTable2], RESEED, 3)
Checking identity information: current identity value '32'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Checking identity information: current identity value '3' czyli zgodnie z tym co zdefiniowaliśmy dla tabeli IdentityTable2 następna wartość będzie 8. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
INSERT INTO [TESTOWA].[dbo].[IdentityTable2]
(name) VALUES ('ANNA'),('KATARZYNA'),('ZOFIA'),('MONIKA'),('TERESA'),('KRYSTYNA');
(6 rows affected)
SELECT * FROM [TESTOWA].[dbo].[IdentityTable2]
id name ----------- -------------------- 2 ADAM 7 PIOTR 8 ANNA 12 ANDRZEJ 13 KATARZYNA 17 KRZYSZTOF 18 ZOFIA 22 JERZY 23 MONIKA 27 MARIUSZ 28 TERESA 32 ROBERT 33 KRYSTYNA (13 rows affected)
Teraz spróbujemy wymusić zapis do tabeli z pominięciem Identity. Wykorzystamy do tego opcję IDENTITY_INSERT
SET IDENTITY_INSERT [TESTOWA].[dbo].[IdentityTable2] ON;
GO
INSERT INTO [TESTOWA].[dbo].[IdentityTable2]
(id,name) VALUES (10,'BORYS');
GO
SET IDENTITY_INSERT [TESTOWA].[dbo].[IdentityTable2] OFF;
GO
Commands completed successfully. (1 row affected) Commands completed successfully.
id name ----------- -------------------- 2 ADAM 7 PIOTR 8 ANNA 10 BORYS 12 ANDRZEJ 13 KATARZYNA 17 KRZYSZTOF 18 ZOFIA 22 JERZY 23 MONIKA 27 MARIUSZ 28 TERESA 32 ROBERT 33 KRYSTYNA (14 rows affected)
Po wyłączeniu (OFF) parametru IDENTITY_INSERT kolejne ID będą przyjmowały wartości zgodnie z definicją Indentity.
INSERT INTO [TESTOWA].[dbo].[IdentityTable2]
(name) VALUES ('TAMARA');
id name ----------- -------------------- 2 ADAM 7 PIOTR 8 ANNA 10 BORYS 12 ANDRZEJ 13 KATARZYNA 17 KRZYSZTOF 18 ZOFIA 22 JERZY 23 MONIKA 27 MARIUSZ 28 TERESA 32 ROBERT 33 KRYSTYNA 38 TAMARA (15 rows affected)
