TechCodeview

La parola chiave IDENTITY è una proprietà in SQL Server. Quando una colonna di tabella viene definita con una proprietà Identity, il suo valore sarà un valore incrementale generato automaticamente . Questo valore viene creato automaticamente dal server. Pertanto, non possiamo inserire manualmente un valore in una colonna di identità come utente. Pertanto, se contrassegniamo una colonna come identità, SQL Server la popolerà in modo con incremento automatico.

Sintassi

Di seguito è riportata la sintassi per illustrare l'utilizzo della proprietà IDENTITY in SQL Server:

 IDENTITY[(seed, increment)] 

I parametri di sintassi di cui sopra sono spiegati di seguito:

Seme:Indica il valore iniziale della riga caricata nella tabella. Per impostazione predefinita, il suo valore è 1.Incremento:Indica il valore incrementale, che viene aggiunto al valore Identity dell'ultima riga caricata. Per impostazione predefinita, il suo valore è 1.

Cerchiamo di comprendere questo concetto attraverso un semplice esempio.

Supponiamo di avere un ' Alunno ' tavolo, e noi vogliamo StudentID da generare automaticamente. Noi abbiamo un ID studente iniziale di 10 e desideri aumentarlo di 1 con ogni nuovo ID. In questo scenario, è necessario definire i seguenti valori.

Seme: 10

Incremento: 1

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

NOTA: in SQL Server è consentita una sola colonna di identificazione per tabella.

Esempio di IDENTITÀ di SQL Server

Cerchiamo di capire come possiamo utilizzare la proprietà identità nella tabella. La proprietà Identity in una colonna può essere impostata al momento della creazione della nuova tabella o dopo la sua creazione. Qui vedremo entrambi i casi con esempi.

Proprietà IDENTITY con nuova tabella

La seguente istruzione creerà una nuova tabella con la proprietà Identity nel database specificato:

 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 ); 

Successivamente, inseriremo una nuova riga in questa tabella con un PRODUZIONE clausola per vedere l'ID persona generato automaticamente:

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

L'esecuzione di questa query visualizzerà l'output seguente:

Questo output mostra che la prima riga è stata inserita con il valore dieci nel file IDPersona colonna come specificato nella colonna identità della definizione di tabella.

Inseriamo un'altra riga nel file tabella delle persone come sotto:

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

Questa query restituirà il seguente output:

Questo output mostra che la seconda riga è stata inserita con il valore 11 e la terza riga con il valore 12 nella colonna PersonID.

Proprietà IDENTITY con tabella esistente

Spiegheremo questo concetto eliminando prima la tabella precedente e creandola senza la proprietà Identity. Esegui l'istruzione seguente per eliminare la tabella:

 DROP TABLE person; 

Successivamente, creeremo una tabella utilizzando la query seguente:

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

Se vogliamo aggiungere una nuova colonna con la proprietà Identity in una tabella esistente, dobbiamo utilizzare il comando ALTER. La query seguente aggiungerà PersonID come colonna di identità nella tabella person:

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

Aggiunta esplicita di valore nella colonna identità

Se aggiungiamo una nuova riga nella tabella precedente specificando esplicitamente il valore della colonna Identity, SQL Server genererà un errore. Vedi la query seguente:

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

L'esecuzione di questa query causerà il seguente errore:

Per inserire esplicitamente il valore della colonna Identity, dobbiamo prima impostare il valore IDENTITY_INSERT su ON. Successivamente, esegui l'operazione di inserimento per aggiungere una nuova riga nella tabella e quindi imposta il valore IDENTITY_INSERT su OFF. Vedi lo script di codice seguente:

 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 ATTIVO consente agli utenti di inserire dati nelle colonne di identità, mentre IDENTITY_INSERT DISATTIVATO impedisce loro di aggiungere valore a questa colonna.

L'esecuzione dello script di codice visualizzerà l'output seguente in cui possiamo vedere che il PersonID con valore 14 è stato inserito correttamente.

Funzione IDENTITÀ

SQL Server fornisce alcune funzioni di identità per l'utilizzo delle colonne IDENTITY in una tabella. Queste funzioni di identità sono elencate di seguito:

1. @@Funzione IDENTITÀ
2. Funzione SCOPE_IDENTITY()
3. Funzione IDENT_CURRENT
4. Funzione IDENTITÀ

Diamo un'occhiata alle funzioni IDENTITY con alcuni esempi.

@@Funzione IDENTITÀ

@@IDENTITY è una funzione definita dal sistema che visualizza l'ultimo valore di identità (valore identità massimo utilizzato) creato in una tabella per la colonna IDENTITY nella stessa sessione. Questa colonna funzione restituisce il valore identità generato dall'istruzione dopo aver inserito una nuova voce in una tabella. Restituisce a NULLO valore quando eseguiamo una query che non crea valori IDENTITY. Funziona sempre nell'ambito della sessione corrente. Non è possibile utilizzarlo da remoto.

Esempio

Supponiamo che il valore di identità massimo corrente nella tabella person sia 13. Ora aggiungeremo un record nella stessa sessione che incrementerà il valore di identità di uno. Quindi utilizzeremo la funzione @@IDENTITY per ottenere l'ultimo valore di identità creato nella stessa sessione.

Ecco lo script completo del codice:

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

L'esecuzione dello script restituirà il seguente output in cui possiamo vedere che il valore di identità massimo utilizzato è 14.

Funzione SCOPE_IDENTITY()

SCOPE_IDENTITY() è una funzione definita dal sistema visualizzare il valore di identità più recente in una tabella nell'ambito attuale. Questo ambito può essere un modulo, un trigger, una funzione o una procedura memorizzata. È simile alla funzione @@IDENTITY(), tranne che questa funzione ha solo un ambito limitato. La funzione SCOPE_IDENTITY restituisce NULL se la eseguiamo prima dell'operazione di inserimento che genera un valore nello stesso ambito.

Esempio

Il codice seguente utilizza sia la funzione @@IDENTITY che SCOPE_IDENTITY() nella stessa sessione. In questo esempio verrà innanzitutto visualizzato l'ultimo valore di identità, quindi verrà inserita una riga nella tabella. Successivamente, esegue entrambe le funzioni di identità.

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

L'esecuzione del codice visualizzerà lo stesso valore nella sessione corrente e un ambito simile. Vedi l'immagine di output qui sotto:

Ora vedremo come le due funzioni sono diverse con un esempio. Per prima cosa creeremo due tabelle denominate dati_impiegato E Dipartimento utilizzando la seguente dichiarazione:

 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 ); 

Successivamente, creiamo un trigger INSERT sulla tabella Employee_data. Questo trigger viene richiamato per inserire una riga nella tabella dipartimento ogni volta che inseriamo una riga nella tabella Employee_data.

La query seguente crea un trigger per l'inserimento di un valore predefinito 'ESSO' nella tabella dipartimento su ogni query di inserimento nella tabella Employee_data:

caricamento di Javascript

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

Dopo aver creato un trigger, inseriremo un record nella tabella Employee_data e vedremo l'output di entrambe le funzioni @@IDENTITY e SCOPE_IDENTITY().

 INSERT INTO employee_data VALUES ('John Mathew'); 

L'esecuzione della query aggiungerà una riga nella tabella Employee_data e genererà un valore Identity nella stessa sessione. Una volta eseguita la query di inserimento nella tabella Employee_Data, viene richiamato automaticamente un trigger per aggiungere una riga nella tabella Department. Il valore seed dell'identità è 1 per Employee_data e 100 per la tabella Department.

Infine, eseguiamo le istruzioni seguenti che visualizzano l'output 100 per la funzione SELECT @@IDENTITY e 1 per la funzione SCOPE_IDENTITY perché restituiscono il valore Identity solo nello stesso ambito.

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

Ecco il risultato:

Funzione IDENT_CURRENT()

IDENT_CURRENT è una funzione definita dal sistema visualizzare il valore IDENTITY più recente generato per una determinata tabella in qualsiasi connessione. Questa funzione non considera l'ambito della query SQL che crea il valore Identity. Questa funzione richiede il nome della tabella per la quale vogliamo ottenere il valore identità.

Esempio

Possiamo capirlo aprendo prima le due finestre di connessione. Inseriremo un record nella prima finestra che genera il valore identità 15 nella tabella persona. Successivamente, possiamo verificare questo valore di identità in un'altra finestra di connessione dove possiamo vedere lo stesso output. Ecco il codice completo:

 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; 

L'esecuzione dei codici sopra in due finestre diverse visualizzerà lo stesso valore di identità.

Funzione IDENTITÀ()

La funzione IDENTITY() è una funzione definita dal sistema utilizzato per inserire una colonna identità in una nuova tabella . Questa funzione è diversa dalla proprietà IDENTITY che utilizziamo con le istruzioni CREATE TABLE e ALTER TABLE. Possiamo utilizzare questa funzione solo in un'istruzione SELECT INTO, che viene utilizzata durante il trasferimento dei dati da una tabella all'altra.

La seguente sintassi illustra l'utilizzo di questa funzione in SQL Server:

 IDENTITY (data_type , seed , increment) AS column_name 

Se una tabella di origine ha una colonna IDENTITY, la tabella formata con un comando SELECT INTO la eredita per impostazione predefinita. Per esempio , abbiamo precedentemente creato una tabella persona con una colonna identità. Supponiamo di creare una nuova tabella che erediti la tabella person utilizzando le istruzioni SELECT INTO con la funzione IDENTITY(). In tal caso, riceveremo un errore perché la tabella di origine ha già una colonna Identity. Vedi la query seguente:

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

L'esecuzione dell'istruzione precedente restituirà il seguente messaggio di errore:

Creiamo una nuova tabella senza proprietà identità utilizzando la seguente istruzione:

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

Quindi, copia questa tabella utilizzando l'istruzione SELECT INTO inclusa la funzione IDENTITY come segue:

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

Una volta eseguita l'istruzione, possiamo verificarla utilizzando il file sp_aiuto comando che visualizza le proprietà della tabella.

Puoi vedere la colonna IDENTITÀ nel file TENTABILE proprietà secondo le condizioni specificate.

Se utilizziamo questa funzione con l'istruzione SELECT, SQL Server visualizzerà il seguente messaggio di errore:

Messaggio 177, livello 15, stato 1, riga 2 La funzione IDENTITY può essere utilizzata solo quando l'istruzione SELECT ha una clausola INTO.

Riutilizzare i valori IDENTITY

Non è possibile riutilizzare i valori Identity nella tabella SQL Server. Quando eliminiamo qualsiasi riga dalla tabella della colonna identità, verrà creato uno spazio vuoto nella colonna identità. Inoltre, SQL Server creerà un intervallo quando inseriamo una nuova riga nella colonna Identity e l'istruzione non riesce o viene ripristinata. Il divario indica che i valori di identità sono andati persi e non possono essere generati nuovamente nella colonna IDENTITY.

Considera l'esempio seguente per capirlo praticamente. Abbiamo già una tabella persona contenente i seguenti dati:

Successivamente, creeremo altre due tabelle denominate 'posizione' , E ' persona_posizione ' utilizzando la seguente istruzione:

 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) ); 

Successivamente, proviamo a inserire un nuovo record nella tabella person e ad assegnargli una posizione aggiungendo una nuova riga nella tabella person_position. Lo faremo utilizzando l'estratto conto della transazione come di seguito:

 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; 

Lo script del codice di transazione riportato sopra esegue correttamente la prima istruzione di inserimento. Ma la seconda istruzione fallì perché non c'era nessuna posizione con id dieci nella tabella delle posizioni. Pertanto, l’intera transazione è stata annullata.

Poiché il valore di identità massimo nella colonna PersonID è 16, la prima istruzione di inserimento ha consumato il valore di identità 17, quindi è stato eseguito il rollback della transazione. Pertanto, se inseriamo la riga successiva nella tabella Persona, il successivo valore identità sarà 18. Esegui l'istruzione seguente:

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

Dopo aver controllato nuovamente la tabella delle persone, vediamo che il record appena aggiunto contiene il valore identità 18.

Due colonne IDENTITY in un'unica tabella

Tecnicamente non è possibile creare due colonne Identity in un'unica tabella. Se lo facciamo, SQL Server genera un errore. Vedi la seguente query:

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

Quando eseguiamo questo codice, vedremo il seguente errore:

Tuttavia, possiamo creare due colonne Identity in un'unica tabella utilizzando la colonna calcolata. La query seguente crea una tabella con una colonna calcolata che utilizza la colonna Identity originale e la diminuisce di 1.

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

Successivamente, aggiungeremo alcuni dati in questa tabella utilizzando il comando seguente:

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

Infine, controlliamo i dati della tabella utilizzando l'istruzione SELECT. Restituisce il seguente output:

Possiamo vedere nell'immagine come la colonna SecondID funge da seconda colonna identità, diminuendo di dieci rispetto al valore iniziale di 9990.

Idee sbagliate sulla colonna IDENTITY di SQL Server

L'utente DBA ha molti malintesi riguardo alle colonne di identità di SQL Server. Di seguito è riportato l'elenco dei malintesi più comuni riguardanti le colonne di identità che verrebbero visualizzati:

La colonna IDENTITY è UNICA: Secondo la documentazione ufficiale di SQL Server, la proprietà Identity non può garantire che il valore della colonna sia univoco. Dobbiamo utilizzare una PRIMARY KEY, un vincolo UNIQUE o un indice UNIQUE per imporre l'unicità della colonna.

La colonna IDENTITY genera numeri consecutivi: La documentazione ufficiale afferma chiaramente che i valori assegnati nella colonna identità possono andare persi in caso di errore del database o riavvio del server. Può causare lacune nel valore Identity durante l'inserimento. Il divario può essere creato anche quando si elimina il valore dalla tabella o si esegue il rollback dell'istruzione di inserimento. I valori che generano lacune non possono essere utilizzati ulteriormente.

La colonna IDENTITY non può generare automaticamente valori esistenti: Non è possibile che la colonna Identity generi automaticamente valori esistenti fino a quando non viene eseguito il reseeding della proprietà Identity utilizzando il comando DBCC CHECKIDENT. Ci consente di regolare il valore seed (valore iniziale della riga) della proprietà Identity. Dopo aver eseguito questo comando, SQL Server non controllerà o meno i valori appena creati già presenti nella tabella.

La colonna IDENTITY come CHIAVE PRIMARIA è sufficiente per identificare la riga: Se una chiave primaria contiene la colonna Identity nella tabella senza altri vincoli univoci, la colonna può archiviare valori duplicati e impedire l'unicità della colonna. Come sappiamo, la chiave primaria non può memorizzare valori duplicati, ma la colonna identità può memorizzare duplicati; si consiglia di non utilizzare la chiave primaria e la proprietà Identity sulla stessa colonna.

Utilizzo dello strumento sbagliato per ripristinare i valori di identità dopo un inserimento: È anche un malinteso comune quello di non conoscere le differenze tra le funzioni @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT e IDENTITY() per ottenere il valore identità inserito direttamente dall'istruzione che abbiamo appena eseguito.

Differenza tra SEQUENZA e IDENTITÀ

Utilizziamo sia SEQUENZA che IDENTITÀ per generare numeri automatici. Tuttavia, presenta alcune differenze e la differenza principale è che l'identità dipende dalla tabella, mentre la sequenza no. Riassumiamo le loro differenze nella forma tabellare:

Conclusione

Questo articolo fornirà una panoramica completa della proprietà IDENTITY in SQL Server. Qui abbiamo imparato come e quando viene utilizzata la proprietà identità, le sue diverse funzioni, le idee sbagliate e come è diversa dalla sequenza.