____________ Rechercher [logo-dvp-h55.png] * Forums * Tutoriels * Magazine * FAQ * Blogs * Chat * Newsletter * Études * Emploi * Club Vous devez avoir un compte Developpez.com et être connecté pour pouvoir participer aux discussions. Identifiez-vous Identifiant ____________________ Mot de passe ____________________ Mot de passe oublié ? [BUTTON Input] (not implemented)________________ Créer un compte Vous n'avez pas encore de compte Developpez.com ? L'inscription est gratuite et ne vous prendra que quelques instants ! Je m'inscris ! Developpez.com Accueil Accueil Forums Emploi Rubriques Choisissez la catégorie, puis la rubrique : * Accueil * ALM + ALM + Merise + UML * Java + Java + Java Web + Spring + Android + Eclipse + NetBeans * .NET + Microsoft DotNET + Visual Studio + ASP.NET + C# + VB.NET + Windows Phone + Microsoft Azure * Dév. Web + Développement Web + AJAX + Apache + ASP + CSS + Dart + Flash / Flex + JavaScript + NodeJS + PHP + Ruby & Rails + TypeScript + Web sémantique + Webmarketing + (X)HTML * EDI + EDI + 4D + Delphi + Eclipse + JetBrains + LabVIEW + NetBeans + MATLAB + Scilab + Visual Studio + WinDev + Visual Basic 6 + Lazarus + Qt Creator * Programmation + Programmation + Débuter - Algorithmique + 2D - 3D - Jeux + Assembleur + C + C++ + D + Go + Kotlin + Objective C + Pascal + Perl + Python + Rust + Swift + Qt + XML + Autres * SGBD + SGBD & SQL + 4D + Access + Big Data + DB2 + Firebird + InterBase + MySQL + NoSQL + PostgreSQL + Oracle + Sybase + SQL-Server * Office + Microsoft Office + Access + Excel + Word + Outlook + PowerPoint + SharePoint + Microsoft Project + OpenOffice & LibreOffice * Solutions d'entreprise + Solutions d'entreprise + Big Data + BPM + Business Intelligence + ERP / PGI + CRM + SAS + Cloud Computing + SAP + Microsoft BizTalk Server + Talend + IBM Cloud + Intelligence artificielle * Applications + Applications + Libres & Open Source + OpenOffice & LibreOffice + Projets * Mobiles + Mobiles + Android + iOS + Windows Phone * Systèmes + Systèmes + Windows + Linux + Arduino + Sécurité + Hardware + HPC + Mac + Raspberry Pi + Réseau + Green IT + Systèmes embarqués + Virtualisation * * ALM + ALM + Merise + UML * Java + Java + Java Web + Spring + Android + Eclipse + NetBeans * .NET + Microsoft DotNET + Visual Studio + ASP.NET + C# + VB.NET + Windows Phone + Microsoft Azure * Dév. Web + Développement Web + AJAX + Apache + ASP + CSS + Dart + Flash / Flex + JavaScript + NodeJS + PHP + Ruby & Rails + TypeScript + Web sémantique + Webmarketing + (X)HTML * EDI + EDI + 4D + Delphi + Eclipse + JetBrains + LabVIEW + NetBeans + MATLAB + Scilab + Visual Studio + WinDev + Visual Basic 6 + Lazarus + Qt Creator * Programmation + Programmation + Débuter - Algorithmique + 2D - 3D - Jeux + Assembleur + C + C++ + D + Go + Kotlin + Objective C + Pascal + Perl + Python + Rust + Swift + Qt + XML + Autres * SGBD + SGBD & SQL + 4D + Access + Big Data + DB2 + Firebird + InterBase + MySQL + NoSQL + PostgreSQL + Oracle + Sybase + SQL-Server * Office + Microsoft Office + Access + Excel + Word + Outlook + PowerPoint + SharePoint + Microsoft Project + OpenOffice & LibreOffice * Solutions d'entreprise + Solutions d'entreprise + Big Data + BPM + Business Intelligence + ERP / PGI + CRM + SAS + Cloud Computing + SAP + Microsoft BizTalk Server + Talend + IBM Cloud + Intelligence artificielle * Applications + Applications + Libres & Open Source + OpenOffice & LibreOffice + Projets * Mobiles + Mobiles + Android + iOS + Windows Phone * Systèmes + Systèmes + Windows + Linux + Arduino + Sécurité + Hardware + HPC + Mac + Raspberry Pi + Réseau + Green IT + Systèmes embarqués + Virtualisation * TUTORIELS * FAQ * LIVRES * TÉLÉCHARGEMENTS * SOURCES * DÉBATS * WIKI * DICO * CALENDRIER * HUMOUR #Le blog de SQLpro » Flux Le blog de SQLpro » Flux des commentaires Les 10 meilleures pratiques pour développer avec un SGBDR Passer d’Access à SQL Server : le piège à c… Le blog de SQLpro Le SQL pour SQL Server, PostGreSQL et tous les autres SGBDR Menu Aller au contenu principal * Accueil Auto incrément IDENTITY avec SQL Server 3 réponses Le mécanisme d’auto incrément de SQL Server via la propriété IDENTITY fait désormais partie de la norme SQL:2003. Cependant il prend quelques distance avec cette dernière et permet de faire plus de choses que la norme n’a prévue… Voyons en détail quels sont les possibilités de cet outil. 1) créer une colonne auto incrémentée La création d’une colonne auto incrémentée se fait avec la propriété IDENTITY dont la syntaxe est la suivante : IDENTITY [ ( #graine , #pas ) ] ou #graine est la valeur de départ et #pas le pas d’incrément. En l’absence des valeurs #graine et #pas le défaut est de 1 pour les deux (commence à 1 avec un pas de 1). Ainsi : IDENTITY (8192, 7) Signifie que l’autoincrément commençera à 8192 et augmentera de 7 à chaque nouvelle ligne insérée. Attention : il ne peut y avoir qu’une seule colonne auto incrémentée par table. Exemple : CREATE TABLE T_CLIENT_CLI (CLI_ID INT NOT NULL IDENTITY(12485, 1) PRIMARY KEY, CLI_NOM CHAR(32) NOT NULL, CLI_PRENOM VARCHAR(25)) Contrairement à une idée reçue, ce n’est pas parce que vous dotez une colonne de la propriété IDENTITY, que cette dernière devient une clef primaire. Deuxième idée reçue, ce n’est pas parce que vous dotez une colonne de la propriété IDENTITY, que les valeurs de cette colonne sont automatiquement unique. 2) insérer des lignes dans une table avec une colonne ayant une propriété IDENTITY Lorsque vous voulez insérer des lignes dans une table pourvues d’un auto incrément, il convient de ne pas préciser cette colonne ou bien d’invoquer le mot clef DEFAULT. Exemple : — syntaxe omettant la liste des colonnes INSERT INTO T_CLIENT_CLI VALUES ('DUPONT', 'Paul'); — syntaxe avec la liste des colonnes non IDENTITY INSERT INTO T_CLIENT_CLI (CLI_NOM, CLI_PRENOM) VALUES ('DUPONT', 'Paul'); Voici le résultat : SELECT * FROM T_CLIENT_CLI CLI_ID CLI_NOM CLI_PRENOM ----------- -------------- ------------ 12486 DUPONT Paul 12487 DUPONT Paul Si votre table est constituée d’une unique colonne avec auto incrément, il faut utiliser la syntaxe suivante : CREATE TABLE T_IDENTITY_IDT (IDT_ID INT IDENTITY); INSERT INTO T_IDENTITY_IDT DEFAULT VALUES; 3) forcer des valeurs dans la colonne ayant la propriété IDENTITY Il est possible de forcer une valeur dans la colonne pourvue de la propriété IDENTITY. Pour ce faire il faut utiliser le flag : SET IDENTITY_INSERT #MaTable { ON | OFF } Ce flag ne peut porter que sur une seule table à la fois. Dans ce cas, les INSERT devront mentionner impérativement la liste des colonnes et la colonne pourvue de la propriété IDENTITY devra y figurer. Exemple : SET IDENTITY_INSERT T_CLIENT_CLI ON; INSERT INTO T_CLIENT_CLI (CLI_ID, CLI_NOM, CLI_PRENOM) VALUES (-1, 'DUPONT', 'Paul'); SET IDENTITY_INSERT T_CLIENT_CLI OFF; Résultat : SELECT * FROM T_CLIENT_CLI CLI_ID CLI_NOM CLI_PRENOM ----------- -------------- ------------ -1 DUPONT Paul 12486 DUPONT Paul 12487 DUPONT Paul Notez que si vous utilisez une valeur supérieur au dernier « jeton » consommé, alors la valeur courante du générateur sera repoussée. Exemple : SET IDENTITY_INSERT T_CLIENT_CLI ON; INSERT INTO T_CLIENT_CLI (CLI_ID, CLI_NOM, CLI_PRENOM) VALUES (1000000, 'DURAND', 'Marc'); SET IDENTITY_INSERT T_CLIENT_CLI OFF; INSERT INTO T_CLIENT_CLI (CLI_NOM, CLI_PRENOM) VALUES ('DURAND', 'Marc'); Comme on le constate, le compteur d’auto incrément a été réinitialisé à la valeur suivant 1000000 : SELECT * FROM T_CLIENT_CLI WHERE CLI_NOM = 'DURAND' CLI_ID CLI_NOM CLI_PRENOM ----------- -------------- ------------ 1000000 DURAND Marc 1000001 DURAND Marc 4) récupérer la valeur du dernier auto incrément SQL Server dispose de 3 outils pour récupérer la valeur du dernier auto incrément. La variable @@IDENTITY fournit la valeur du dernier auto incrément de la session de l’utilisateur quelque soit la table qui a été auto incrémentée, avec une visibilité limitée à la session propre à l’utilisateur La fonction SCOPE_IDENTITY() (pas d’argument) fournit la valeur du dernier auto incrément de la session de l’utilisateur dans l’étendue de code quelque soit la table qui a été auto incrémentée. Enfin, IDENT_CURRENT(‘nom_table‘) permet de connaître la valeur du dernier incrément généré sur une table quelque soit l’utilisateur qui l’a fait générer. Exemple : INSERT INTO T_CLIENT_CLI (CLI_NOM, CLI_PRENOM) VALUES ('DUVAL', 'Luc'); SELECT @@IDENTITY AS IDENT, SCOPE_IDENTITY() AS SCOP_IDENT, IDENT_CURRENT('dbo.T_CLIENT_CLI') AS CURR_IDENT IDENT SCOP_IDENT CURR_IDENT --------- ------------ ------------ 1000002 1000002 1000002 La différence entre @@IDENTITY et SCOPE_IDENTITY() est subtile. En effet, si l’insertion dans votre table déclenche un trigger qui insère des données dans une autre table (trigger AFTER INSERT) alors la variable @@IDENTITY contient l’incrément de cet autre table et la fonction SCOPE_IDENTITY() renvoie le dernier incrément visible dans l’unité de code (fonction, trigger, procédure…). Démonstration : INSERT INTO T_CLIENT_CLI (CLI_NOM, CLI_PRENOM) VALUES ('DUBOIS', 'Marie'); SELECT @@IDENTITY AS IDENT, SCOPE_IDENTITY() AS SCOP_IDENT, IDENT_CURRENT('master.sys.databases') AS CURR_IDENT IDENT SCOP_IDENT CURR_IDENT --------- ------------ ------------ 2 1000003 NULL 5) réinitialiser les paramètres du compteur IDENTITY Il est possible de réinitialiser les paramètres du compteur à l’aide d’une commande du DBCC (DataBase Consol Command) de nom CHECKIDENT : DBCC CHECKIDENT ( 'nomTable' [ , { NORESEED | { RESEED [ , nouvelle_graine ] } } ] ) [ WITH NO_INFOMSGS ] Exemple : DBCC CHECKIDENT('T_CLIENT_CLI', RESEED, 123 ); INSERT INTO T_CLIENT_CLI (CLI_NOM, CLI_PRENOM) VALUES ('WELLES', 'Orson'); SELECT * FROM T_CLIENT_CLI WHERE CLI_NOM = 'WELLES'; CLI_ID CLI_NOM CLI_PRENOM ----------- ------------- ---------------- 124 WELLES Orson 6) IDENTITY et ROLLBACK Lorsque des transactions sont annulées (ROLLBACK) les valeurs des id « consommées » par les auto incrément ne sont pas récupérées. En effet le ROLLBACK ne concerne pas le mécanisme d’auto incrément (et heureusement, sinon, gare au paradoxe temporel…) Démonstration : -- Création des objets Code : CREATE DATABASE DB_TEST; GO USE DB_TEST; GO CREATE TABLE T (C INT IDENTITY); GO -- primo insertion Code : INSERT INTO T DEFAULT VALUES; GO SELECT * FROM T; GO C ----------- 1 -- insertion annulée dans une transaction Code : BEGIN TRANSACTION INSERT INTO T DEFAULT VALUES; ROLLBACK TRANSACTION; GO SELECT * FROM T; GO C ----------- 1 -- troisième insertion : Code : INSERT INTO T DEFAULT VALUES; GO SELECT * FROM T; GO C ----------- 1 3 On voit bien que le « jeton » n°2 a été consommé par la transaction annulée ! 7) accéder à la colonne autoincrémentée sans connaître son nom : Dans une requête il est possible d’accéder à la colonne autoincrémentée (via IDENTITY) sans pour autant avoir besoin de connaître son nom. Il suffit d’utiliser la pseudo colonne $IDENTITY. Exemple : Partant de la table : CREATE TABLE T_CLIENT_CLI (CLI_ID INT NOT NULL IDENTITY(12485, 1) PRIMARY KEY, CLI_NOM CHAR(32) NOT NULL, CLI_PRENOM VARCHAR(25)) Dans laquelle aucune insertion n’a encore eut lieu, faisons : SELECT MIN($IDENTITY) AS MIN_ID, MAX($IDENTITY) AS MAX_ID FROM T_CLIENT_CLI Résultat : MIN_ID MAX_ID ----------- ----------- NULL NULL Insérons quelques résultats : INSERT INTO T_CLIENT_CLI VALUES ('DUPONT', 'Paul'), ('DUVAL', 'Marc'), ('DUFOUR', 'Jean'); Recommençons la requête SELECT : SELECT MIN($IDENTITY) AS MIN_ID, MAX($IDENTITY) AS MAX_ID FROM T_CLIENT_CLI Résultat : MIN_ID MAX_ID ———– ———– 1 3 8) Résumés des outils de manipulation de l’auto incrément : Génération d’auto incrément : IDENTITY [ ( graine [, pas ] ) ] Propriété autoincrément de colonne avec graine et pas de type entier (INT, BIGINT) - Utilisée avec CREATE et ALTER TABLE. IDENTITY [ ( graine [, pas ] ) ] Fonction d'autoincrémentation pour une colonne de table créée à la volée - Utilisée uniquement avec SELECT INTO SET IDENTITY INSERT { ON | OFF } Débranchement (ON) ou branchement (OFF) de l'autoincrément pour forçage de valeurs Lecture de la valeur de l’auto incrément : @@IDENTITY Variable globale de session contenant la valeur du dernier autoincrément inséré SCOPE_IDENTITY() Fonction de session contenant la valeur du dernier autoincrément inséré dans l'unité de code IDENT_CURRENT('') Fonction retournant la valeur de l'autoincrément courant de la table passée en argument $IDENTITY pseudo colonne permettant la lecture directe de la valeur de l'autoincrément dans une requête SELECT appliquée à la table concernée Pilotage de l’auto incrémentation : DBCC CHECKIDENT ('' Vérifie la valeur e l'autoincrément courant pour la table spécifiée et, si nécessaire, modifie cette valeur [ , { NORESEED | { RESEED [ , nouvelle_graine ] } } ] ) [ WITH NO_INFOMSGS ] IDENT_INCR('') Renvoie la valeur d'incrément (pas) de l'autoincrément de la table considérée IDENT_SEED('') Renvoie la valeur de la graine de l'autoincrément de la table considérée Méta données : sys.identity_columns Vue système donnant la liste des colonnes autoincrémentées par objet COLUMNPROPERTY Fonction retournant 1 si la colonne (column_name) de la table (object_id) est autoincrémentée (object_id , column_name , 'IsIdentity') sys.columns / is_identity Vue renseignant dans la colonne is_identity quelle colonne de la table est autoincrémentée -------- Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * * MVP Microsoft SQL Server Cette entrée a été publiée dans MS SQL Server le 2 janvier 2011 par SQLpro. Navigation des articles ← Les 10 meilleures pratiques pour développer avec un SGBDR Passer d’Access à SQL Server : le piège à c… → 3 réflexions au sujet de « Auto incrément IDENTITY avec SQL Server » 1. Avatar de CinePhil CinePhil 4 octobre 2017 à 12 h 36 min Quelques questions générales sur IDENTITY / Séquence / AUTO_INCREMENT… 1) Si je comprends bien, IDENTITY est une séquence interne à la table qui n’est pas visible en tant qu’objet indépendant, contrairement à la séquence (Oracle ou Postgresql). L’AUTO_INCREMENT de MySQL serait donc du même genre que l’IDENTITY de SQL Server ? (Oui, je sais que l’AUTO_INCREMENT de MySQMerde est pourri, j’en ai fait l’expérience récemment lors d’insertions en masse dans une table) :) 2) Il m’est arrivé plusieurs fois dans une base Oracle que des données aient été insérées dans une table sans utiliser la séquence associée. Le LAST_NUMBER de la séquence était ainsi inférieur au MAX(colonne_cle_primaire) de la table. Conséquence : lors de l’insertion d’une ligne en utilisant la séquence, cette dernière délivrait un numéro déjà présent dans la clé primaire ! Aïe ! Obligé de passer une série de requêtes SELECT sequence.next() FROM DUAL pour monter artificiellement le LAST_NUMBER de la séquence au MAX(colonne_cle_primaire) de la table. Cela peut-il arriver aussi avec IDENTITY ou bien le SGBDR tient compte de ce qui inséré dans la colonne IDENTITY pour déterminer le prochain numéro à délivrer ? Connectez-vous pour répondre ↓ 2. Avatar de SQLpro SQLpro Auteur de l’article29 juillet 2015 à 23 h 16 min Il faut passer par une recréation de la table : 1) la recréer sous nom_table_bis avec la colonne IDENTITY 2) migrer les données de l’ancienne à la nouvelle table 3) la renommer avec sp_rename A + Connectez-vous pour répondre ↓ 3. Avatar de win_ubuntu win_ubuntu 8 mars 2015 à 21 h 25 min comment ajouter la contrainte identity à une colonne existante?: alter table nom_table add identity … Connectez-vous pour répondre ↓ Laisser un commentaire Annuler la réponse. Vous devez être connecté(e) pour rédiger un commentaire. Rechercher : ____________________ Rechercher Articles récents * Modèle générique vs modèle spécifique * Cosmos DB, la base NoSQL à tout faire ! * Une peste nommée NOLOCK… et comment l’éviter * Comparer deux requêtes aux résultats identiques * SQL Server 2017 comparatif performances Linux vs Windows Commentaires récents * gnicolas dans Modèle générique vs modèle spécifique * SQLpro dans Gestion générique des tables de référence * SQLpro dans SGBDR et virtualisation * SQLpro dans Fragmentation physique des fichiers et temps de réponse * Nico.Bizz dans Fragmentation physique des fichiers et temps de réponse Archives * août 2018 * juillet 2018 * mai 2018 * mars 2018 * février 2018 * décembre 2017 * novembre 2017 * octobre 2017 * juillet 2017 * mai 2017 * janvier 2017 * octobre 2016 * mai 2016 * février 2016 * décembre 2015 * novembre 2015 * octobre 2015 * septembre 2015 * août 2015 * juillet 2015 * juin 2015 * mars 2015 * janvier 2015 * décembre 2014 * octobre 2014 * septembre 2014 * juillet 2014 * juin 2014 * mai 2014 * avril 2014 * mars 2014 * février 2014 * décembre 2013 * août 2013 * mai 2013 * avril 2013 * mars 2013 * février 2013 * octobre 2012 * août 2012 * juillet 2012 * juin 2012 * mai 2012 * avril 2012 * mars 2012 * février 2012 * janvier 2012 * décembre 2011 * novembre 2011 * octobre 2011 * septembre 2011 * août 2011 * juillet 2011 * juin 2011 * mai 2011 * avril 2011 * mars 2011 * février 2011 * janvier 2011 * novembre 2010 * octobre 2010 * septembre 2010 * août 2010 * juillet 2010 * mai 2010 * mars 2010 * février 2010 * janvier 2010 * décembre 2009 * novembre 2009 * octobre 2009 * septembre 2009 * juillet 2009 * juin 2009 * mai 2009 * avril 2009 * mars 2009 * février 2009 * janvier 2009 * décembre 2008 * novembre 2008 * octobre 2008 * septembre 2008 * août 2008 Catégories * bases de données * Langage SQL (norme) * Modélisation des donées * MS SQL Server * Oracle * PostGreSQL * SQL Server 2000 * SQL Server 2005 * SQL Server 2008 * SQL Server 2012 * SQL Server 2014 * SQL Server 2016 * SQL Server 2017 Méta * Connexion * Flux RSS des articles * RSS des commentaires * Site de WordPress-FR Contacter le responsable de la rubrique Accueil * Nous contacter * Participez * Hébergement * Informations légales * Partenaire : Hébergement Web © 2000-2019 - www.developpez.com Partenaire : Hébergement Web