TechCodeview

Le mot clé IDENTITY est une propriété dans SQL Server. Lorsqu'une colonne de table est définie avec une propriété d'identité, sa valeur sera une valeur incrémentielle générée automatiquement . Cette valeur est créée automatiquement par le serveur. Par conséquent, nous ne pouvons pas saisir manuellement une valeur dans une colonne d’identité en tant qu’utilisateur. Par conséquent, si nous marquons une colonne comme identité, SQL Server la remplira de manière auto-incrémentée.

Syntaxe

Voici la syntaxe pour illustrer l'utilisation de la propriété IDENTITY dans SQL Server :

 IDENTITY[(seed, increment)] 

Les paramètres de syntaxe ci-dessus sont expliqués ci-dessous :

Graine:Il indique la valeur de départ de la ligne chargée dans le tableau. Par défaut, sa valeur est 1.Incrément:Il indique la valeur incrémentielle, qui s'ajoute à la valeur d'identité de la dernière ligne chargée. Par défaut, sa valeur est 1.

Comprenons ce concept à travers un exemple simple.

Supposons que nous ayons un ' Étudiant ' table, et nous voulons Carte d'étudiant à générer automatiquement. Nous avons un carte d'étudiant débutant de 10 et je souhaite l’augmenter de 1 à chaque nouvel identifiant. Dans ce scénario, les valeurs suivantes doivent être définies.

Graine: dix

Incrément: 1

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

REMARQUE : Une seule colonne d'identification est autorisée par table dans SQL Server.

Exemple d'IDENTITÉ SQL Server

Voyons comment nous pouvons utiliser la propriété d'identité dans le tableau. La propriété d'identité dans une colonne peut être définie soit lors de la création de la nouvelle table, soit après sa création. Ici, nous verrons les deux cas avec des exemples.

Propriété IDENTITÉ avec nouvelle table

L'instruction suivante créera une nouvelle table avec la propriété d'identité dans la base de données spécifiée :

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

Ensuite, nous insérerons une nouvelle ligne dans ce tableau avec un SORTIR clause pour voir l'identifiant de personne généré automatiquement :

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

L'exécution de cette requête affichera le résultat ci-dessous :

Cette sortie montre que la première ligne a été insérée avec la valeur dix dans le ID de personne colonne comme spécifié dans la colonne d'identité de la définition de table.

Insérons une autre ligne dans le table de personne comme ci-dessous :

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

Cette requête renverra le résultat suivant :

Cette sortie montre que la deuxième ligne a été insérée avec la valeur 11 et la troisième ligne avec la valeur 12 dans la colonne PersonID.

Propriété IDENTITY avec table existante

Nous expliquerons ce concept en supprimant d'abord le tableau ci-dessus et en les créant sans propriété d'identité. Exécutez l'instruction ci-dessous pour supprimer la table :

 DROP TABLE person; 

Ensuite, nous allons créer une table en utilisant la requête ci-dessous :

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

Si nous voulons ajouter une nouvelle colonne avec la propriété d'identité dans une table existante, nous devons utiliser la commande ALTER. La requête ci-dessous ajoutera le PersonID en tant que colonne d'identité dans la table des personnes :

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

Ajouter explicitement de la valeur dans la colonne d'identité

Si nous ajoutons une nouvelle ligne dans le tableau ci-dessus en spécifiant explicitement la valeur de la colonne d'identité, SQL Server générera une erreur. Voir la requête ci-dessous :

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

L'exécution de cette requête entraînera l'erreur suivante :

Pour insérer explicitement la valeur de la colonne d'identité, nous devons d'abord définir la valeur IDENTITY_INSERT sur ON. Ensuite, exécutez l'opération d'insertion pour ajouter une nouvelle ligne dans la table, puis définissez la valeur IDENTITY_INSERT sur OFF. Voir le script de code ci-dessous :

 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 SUR permet aux utilisateurs de placer des données dans des colonnes d'identité, tandis que IDENTITY_INSERT DÉSACTIVÉ les empêche d’ajouter de la valeur à cette colonne.

L'exécution du script de code affichera la sortie ci-dessous où nous pouvons voir que le PersonID avec la valeur 14 est inséré avec succès.

Fonction IDENTITÉ

SQL Server fournit certaines fonctions d'identité pour travailler avec les colonnes IDENTITY dans une table. Ces fonctions d'identité sont répertoriées ci-dessous :

1. @@Fonction IDENTITÉ
2. Fonction SCOPE_IDENTITY()
3. Fonction IDENT_CURRENT
4. Fonction IDENTITÉ

Jetons un coup d'œil aux fonctions IDENTITY avec quelques exemples.

@@Fonction IDENTITÉ

@@IDENTITY est une fonction définie par le système qui affiche la dernière valeur d'identité (valeur d'identité maximale utilisée) créée dans une table pour la colonne IDENTITY dans la même session. Cette colonne de fonction renvoie la valeur d'identité générée par l'instruction après l'insertion d'une nouvelle entrée dans une table. Il renvoie un NUL valeur lorsque nous exécutons une requête qui ne crée pas de valeurs IDENTITY. Cela fonctionne toujours dans le cadre de la session en cours. Il ne peut pas être utilisé à distance.

Exemple

Supposons que la valeur d'identité maximale actuelle dans la table des personnes soit de 13. Nous allons maintenant ajouter un enregistrement dans la même session qui incrémente la valeur d'identité de un. Ensuite, nous utiliserons la fonction @@IDENTITY pour obtenir la dernière valeur d'identité créée dans la même session.

Voici le script de code complet :

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

L'exécution du script renverra la sortie suivante où nous pouvons voir que la valeur d'identité maximale utilisée est de 14.

Fonction SCOPE_IDENTITY()

SCOPE_IDENTITY() est une fonction définie par le système pour afficher la valeur d'identité la plus récente dans un tableau sous la portée actuelle. Cette portée peut être un module, un déclencheur, une fonction ou une procédure stockée. Elle est similaire à la fonction @@IDENTITY(), sauf que cette fonction n'a qu'une portée limitée. La fonction SCOPE_IDENTITY renvoie NULL si nous l'exécutons avant l'opération d'insertion qui génère une valeur dans la même portée.

Exemple

Le code ci-dessous utilise à la fois les fonctions @@IDENTITY et SCOPE_IDENTITY() dans la même session. Cet exemple affichera d'abord la dernière valeur d'identité, puis insérera une ligne dans le tableau. Ensuite, il exécute les deux fonctions d’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'exécution du code affichera la même valeur dans la session en cours et une portée similaire. Voir l'image de sortie ci-dessous :

Nous allons maintenant voir en quoi les deux fonctions sont différentes avec un exemple. Tout d’abord, nous allons créer deux tables nommées données_employé et département en utilisant la déclaration ci-dessous :

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

Ensuite, nous créons un déclencheur INSERT sur la table Employee_data. Ce déclencheur est invoqué pour insérer une ligne dans la table Department chaque fois que nous insérons une ligne dans la table Employee_data.

La requête ci-dessous crée un déclencheur pour insérer une valeur par défaut 'IL' dans la table Department à chaque requête d'insertion dans la table Employee_data :

chemin défini en Java

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

Après avoir créé un déclencheur, nous allons insérer un enregistrement dans la table Employee_data et voir le résultat des fonctions @@IDENTITY et SCOPE_IDENTITY().

 INSERT INTO employee_data VALUES ('John Mathew'); 

L'exécution de la requête ajoutera une ligne dans la table Employee_data et générera une valeur d'identité dans la même session. Une fois la requête d'insertion exécutée dans la table Employee_data, elle appelle automatiquement un déclencheur pour ajouter une ligne dans la table Department. La valeur de départ d'identité est 1 pour la table Employee_data et 100 pour la table Department.

Enfin, nous exécutons les instructions ci-dessous qui affichent la sortie 100 pour la fonction SELECT @@IDENTITY et 1 pour la fonction SCOPE_IDENTITY car elles renvoient la valeur d'identité dans la même portée uniquement.

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

Voici le résultat :

Fonction IDENT_CURRENT()

IDENT_CURRENT est une fonction définie par le système pour afficher la valeur d'IDENTITÉ la plus récente généré pour une table donnée sous n’importe quelle connexion. Cette fonction ne prend pas en compte la portée de la requête SQL qui crée la valeur d'identité. Cette fonction nécessite le nom de la table pour laquelle nous souhaitons obtenir la valeur d'identité.

Exemple

On peut le comprendre en ouvrant d'abord les deux fenêtres de connexion. Nous allons insérer un enregistrement dans la première fenêtre qui génère la valeur d'identité 15 dans la table des personnes. Ensuite, nous pouvons vérifier cette valeur d'identité dans une autre fenêtre de connexion où nous pouvons voir le même résultat. Voici le code complet :

 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'exécution des codes ci-dessus dans deux fenêtres différentes affichera la même valeur d'identité.

Fonction IDENTITÉ()

La fonction IDENTITY() est une fonction définie par le système utilisé pour insérer une colonne d'identité dans une nouvelle table . Cette fonction est différente de la propriété IDENTITY que nous utilisons avec les instructions CREATE TABLE et ALTER TABLE. Nous ne pouvons utiliser cette fonction que dans une instruction SELECT INTO, qui est utilisée lors du transfert de données d'une table à une autre.

La syntaxe suivante illustre l'utilisation de cette fonction dans SQL Server :

 IDENTITY (data_type , seed , increment) AS column_name 

Si une table source possède une colonne IDENTITY, la table formée avec une commande SELECT INTO en hérite par défaut. Par exemple , nous avons préalablement créé une table person avec une colonne identité. Supposons que nous créions une nouvelle table qui hérite de la table des personnes à l'aide des instructions SELECT INTO avec la fonction IDENTITY(). Dans ce cas, nous obtiendrons une erreur car la table source possède déjà une colonne d’identité. Voir la requête ci-dessous :

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

L'exécution de l'instruction ci-dessus renverra le message d'erreur suivant :

Créons une nouvelle table sans propriété d'identité en utilisant l'instruction ci-dessous :

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

Ensuite, copiez ce tableau à l'aide de l'instruction SELECT INTO incluant la fonction IDENTITY comme suit :

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

Une fois l'instruction exécutée, nous pouvons la vérifier en utilisant le sp_help commande qui affiche les propriétés de la table.

Vous pouvez voir la colonne IDENTITÉ dans le TENTABLE propriétés selon les conditions spécifiées.

Si nous utilisons cette fonction avec l'instruction SELECT, SQL Server affichera le message d'erreur suivant :

Msg 177, niveau 15, état 1, ligne 2 La fonction IDENTITY ne peut être utilisée que lorsque l'instruction SELECT a une clause INTO.

Réutiliser les valeurs d'IDENTITÉ

Nous ne pouvons pas réutiliser les valeurs d'identité dans la table SQL Server. Lorsque nous supprimons une ligne de la table de la colonne d'identité, un espace est créé dans la colonne d'identité. De plus, SQL Server créera un espace lorsque nous insérons une nouvelle ligne dans la colonne d'identité et que l'instruction échoue ou est annulée. L'espace indique que les valeurs d'identité sont perdues et ne peuvent pas être générées à nouveau dans la colonne IDENTITÉ.

Considérez l'exemple ci-dessous pour le comprendre pratiquement. Nous avons déjà une table de personnes contenant les données suivantes :

Ensuite, nous allons créer deux autres tables nommées 'position' , et ' personne_position ' en utilisant l'instruction suivante :

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

Ensuite, nous essayons d'insérer un nouvel enregistrement dans la table person et de lui attribuer une position en ajoutant une nouvelle ligne dans la table person_position. Nous ferons cela en utilisant le relevé de transaction comme ci-dessous :

 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; 

Le script de code de transaction ci-dessus exécute avec succès la première instruction d'insertion. Mais la deuxième instruction a échoué car il n’y avait aucune position avec l’identifiant dix dans la table des positions. Par conséquent, l’intégralité de la transaction a été annulée.

Étant donné que la valeur d'identité maximale dans la colonne PersonID est de 16, la première instruction d'insertion a consommé la valeur d'identité de 17, puis la transaction a été annulée. Par conséquent, si nous insérons la ligne suivante dans la table Person, la prochaine valeur d'identité sera 18. Exécutez l'instruction ci-dessous :

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

Après avoir vérifié à nouveau la table des personnes, nous constatons que l'enregistrement nouvellement ajouté contient la valeur d'identité 18.

Deux colonnes IDENTITY dans une seule table

Techniquement, il n'est pas possible de créer deux colonnes d'identité dans une seule table. Si nous faisons cela, SQL Server génère une erreur. Voir la requête suivante :

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

Lorsque nous exécutons ce code, nous verrons l'erreur suivante :

Cependant, nous pouvons créer deux colonnes d'identité dans une seule table en utilisant la colonne calculée. La requête suivante crée une table avec une colonne calculée qui utilise la colonne d'identité d'origine et la diminue de 1.

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

Ensuite, nous ajouterons quelques données dans ce tableau à l'aide de la commande ci-dessous :

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

Enfin, nous vérifions les données de la table à l'aide de l'instruction SELECT. Il renvoie le résultat suivant :

Nous pouvons voir dans l'image comment la colonne SecondID agit comme une deuxième colonne d'identité, diminuant de dix par rapport à la valeur de départ de 9990.

Idées fausses sur la colonne IDENTITY de SQL Server

L'utilisateur DBA a de nombreuses idées fausses concernant les colonnes d'identité de SQL Server. Voici la liste des idées fausses les plus courantes concernant les colonnes d'identité qui pourraient être vues :

La colonne IDENTITÉ est UNIQUE : Selon la documentation officielle de SQL Server, la propriété d'identité ne peut pas garantir que la valeur de la colonne est unique. Nous devons utiliser une PRIMARY KEY, une contrainte UNIQUE ou un index UNIQUE pour renforcer l'unicité des colonnes.

La colonne IDENTITY génère des nombres consécutifs : La documentation officielle indique clairement que les valeurs attribuées dans la colonne d'identité peuvent être perdues en cas de panne de base de données ou de redémarrage du serveur. Cela peut entraîner des lacunes dans la valeur d'identité lors de l'insertion. L'écart peut également être créé lorsque nous supprimons la valeur de la table ou que l'instruction d'insertion est annulée. Les valeurs qui génèrent des écarts ne peuvent plus être utilisées.

La colonne IDENTITY ne peut pas générer automatiquement les valeurs existantes : Il n'est pas possible pour la colonne d'identité de générer automatiquement les valeurs existantes tant que la propriété d'identité n'est pas réamorcée à l'aide de la commande DBCC CHECKIDENT. Cela nous permet d'ajuster la valeur de départ (valeur de départ de la ligne) de la propriété d'identité. Après avoir exécuté cette commande, SQL Server ne vérifiera pas les valeurs nouvellement créées déjà présentes ou non dans la table.

La colonne IDENTITY en tant que CLÉ PRIMAIRE suffit à identifier la ligne : Si une clé primaire contient la colonne d'identité dans la table sans aucune autre contrainte unique, la colonne peut stocker des valeurs en double et empêcher l'unicité de la colonne. Comme nous le savons, la clé primaire ne peut pas stocker de valeurs en double, mais la colonne d'identité peut stocker des doublons ; il est recommandé de ne pas utiliser la clé primaire et la propriété d'identité sur la même colonne.

Utiliser le mauvais outil pour récupérer les valeurs d'identité après une insertion : Il s'agit également d'une idée fausse courante concernant la méconnaissance des différences entre les fonctions @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT et IDENTITY() pour obtenir la valeur d'identité directement insérée à partir de l'instruction que nous venons d'exécuter.

Différence entre SÉQUENCE et IDENTITÉ

Nous utilisons à la fois SEQUENCE et IDENTITY pour générer des numéros automatiques. Cependant, il présente quelques différences, et la principale différence est que l'identité dépend de la table, alors que la séquence ne l'est pas. Résumons leurs différences sous forme de tableau :

Conclusion

Cet article donnera un aperçu complet de la propriété IDENTITY dans SQL Server. Ici, nous avons appris comment et quand la propriété d'identité est utilisée, ses différentes fonctions, ses idées fausses et en quoi elle diffère de la séquence.