IDENTITY property

sfatiamo un mito...se ce ne fosse ancora bisogno: una colonna di tabella definita come con la proprietà IDENTITY ammette:
a) valori negativi (a meno che di tipo TINYINT poichè ammette numeri interi da 0 a 255);
b) valori duplicati.

E' bastata la curiosità di una partecipante al corso che sto tenendo in questi giorni, a far emergere in aula questi due tipici dubbi, il secondo dei quali è causato, molto probabilmente, dal fatto che una colonna IDENTITY viene usata solitamente come PRIMARY KEY della tabella, assicurando come tale l'univocità dei valori in colonna. Un esempio:

USE tempdb
GO

DROP TABLE T1;
CREATE TABLE T1 (
    col1_identity INT IDENTITY(1,1),
    col2 VARCHAR(10)
)
GO

--inserisco 5 righe che porteranno il valore corrente identity a 5
INSERT T1 (col2) VALUES (NULL)
GO 5

SELECT * FROM T1
GO

--impostazione necessaria per inserire esplicitamente valori nella colonna identity
SET IDENTITY_INSERT T1 ON
GO

--inserisco dei valori duplicati nella colonna identity
INSERT T1 (col1_identity, col2) VALUES (1, '2nd uno')
INSERT T1 (col1_identity, col2) VALUES (2, '2nd due')
--inserisco un valore negativo nella colonna identity
INSERT T1 (col1_identity, col2) VALUES (-5, 'meno 5')

SELECT * FROM T1
GO

--vediamo qual'è il valore corrente della colonna identity, quindi la reimpostiamo a -5, infine la ricontrolliamo
DBCC CHECKIDENT (T1, NORESEED)
DBCC CHECKIDENT (T1, RESEED, -5)
DBCC CHECKIDENT (T1, NORESEED)
--ok è -5
GO

--ritorniamo come prima a NON poter inserire esplicitamente valori nella colonna identity
SET IDENTITY_INSERT T1 OFF
GO

--inserisco 5 righe che porteranno il valore corrente identity da -5 a 0
INSERT T1 (col2) VALUES ('negative!');
GO 5

SELECT * FROM T1;
GO

Circa il fatto che ammette valori negativi, la cosa può tornare utile in quanto possiamo ottimizzare l'utilizzo del range di valori ammessi dal tipo di dato con cui definiamo la colonna. Prendiamo, per esempio, il tipo SMALLINT che occupa 2 byte e ammette da (-32,768) a (32,767). Possiamo "sfruttare" completamente tale range partendo proprio dal suo limite inferiore:

DROP TABLE T2;
CREATE TABLE T2 (
    ID SMALLINT IDENTITY (-32768, 1),
    Des VARCHAR(10)
)
GO

INSERT T2 (Des) VALUES (NULL)
GO 10

SELECT * FROM T2
GO