Zastosowanie IDENTITY w tabelach

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)
Ten wpis został opublikowany w kategorii Microsoft SQL. Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz