Question Réinitialiser la graine d'identité après la suppression d'enregistrements dans SQL Server


J'ai inséré des enregistrements dans une table de base de données SQL Server. La table a une clé primaire définie et la graine d'identité d'incrémentation automatique est définie sur "Oui". Cela est principalement dû au fait que dans SQL Azure, chaque table doit avoir une clé primaire et une identité définies.

Mais comme je dois supprimer certains enregistrements de la table, la graine d'identité pour ces tables sera perturbée et la colonne d'index (qui est générée automatiquement avec un incrément de 1) sera perturbée.

Comment puis-je réinitialiser la colonne d'identité après avoir supprimé les enregistrements pour que la colonne ait une séquence dans l'ordre numérique croissant?

La colonne d'identité n'est pas utilisée comme clé étrangère n'importe où dans la base de données.


491
2018-02-17 08:51


origine


Réponses:


le DBCC CHECKIDENT La commande de gestion permet de réinitialiser le compteur d'identité. La syntaxe de la commande est:

DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}])
[ WITH NO_INFOMSGS ]

Exemple:

DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
GO

Il n'était pas pris en charge dans les versions précédentes d'Azure SQL Database, mais il est désormais pris en charge.


Veuillez noter que new_reseed_value l'argument est varié entre les versions de SQL Server selon la documentation:

Si des lignes sont présentes dans la table, la ligne suivante est insérée avec le new_reseed_value valeur. Dans la version SQL Server 2008 R2 et versions antérieures, la ligne suivante insérée utilise new_reseed_value + la valeur d'incrémentation actuelle.

cependant, Je trouve cette information trompeuse (simplement faux) car le comportement observé indique qu'au moins SQL Server 2012 est toujours utilisé new_reseed_value + la logique de la valeur d'incrément actuelle. Microsoft contredit même avec ses propres Example C trouvé sur la même page:

C. Forcer la valeur d'identité actuelle à une nouvelle valeur

L'exemple suivant force la valeur d'identité actuelle dans le   Colonne AddressTypeID de la table AddressType à une valeur de 10.   Parce que la table a des lignes existantes, la ligne suivante insérée utilisera 11   comme valeur, c'est-à-dire la nouvelle valeur d'incrément de courant définie pour   valeur de la colonne plus 1.

USE AdventureWorks2012;  
GO  
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);  
GO

Pourtant, tout cela laisse une option pour un comportement différent sur les versions plus récentes de SQL Server. Je pense que la seule façon de s’assurer, jusqu’à ce que Microsoft clarifie les choses dans sa propre documentation, est de faire des tests avant de les utiliser.


825
2018-02-17 09:04



DBCC CHECKIDENT ('TestTable', RESEED, 0)
GO

Où 0 est identity Valeur de départ


176
2018-02-17 08:59



Il convient de noter que SI tout des données est retiré de la table via le DELETE (c'est-à-dire WHERE clause), aussi longtemps que a) les autorisations le permettent, et b) il n'y a pas de FK référençant la table (ce qui semble être le cas ici), en utilisant TRUNCATE TABLE serait préférable car il est plus efficace DELETE  et réinitialise le IDENTITY graine en même temps. Les détails suivants proviennent de la page MSDN pour TABLEAU TRUNCATE:

Par rapport à l'instruction DELETE, TRUNCATE TABLE présente les avantages suivants:

  • Moins d'espace de journal des transactions est utilisé.

    L'instruction DELETE supprime les lignes une par une et enregistre une entrée dans le journal des transactions pour chaque ligne supprimée. TRUNCATE TABLE supprime les données en libérant les pages de données utilisées pour stocker les données de la table et enregistre uniquement les désallocations de la page dans le journal des transactions.

  • Moins de verrous sont généralement utilisés.

    Lorsque l'instruction DELETE est exécutée à l'aide d'un verrou de ligne, chaque ligne de la table est verrouillée pour suppression. TRUNCATE TABLE verrouille toujours la table (y compris un verrou de schéma (SCH-M)) et une page mais pas chaque ligne.

  • Sans exception, zéro pages sont laissées dans la table.

    Une fois qu'une instruction DELETE est exécutée, la table peut toujours contenir des pages vides. Par exemple, les pages vides d'un tas ne peuvent pas être désallouées sans au moins un verrou de table exclusif (LCK_M_X). Si l'opération de suppression n'utilise pas de verrou de table, la table (tas) contiendra de nombreuses pages vides. Pour les index, l'opération de suppression peut laisser des pages vides derrière, bien que ces pages soient rapidement désallouées par un processus de nettoyage en arrière-plan.

Si la table contient une colonne d'identité, le compteur de cette colonne est réinitialisé à la valeur de départ définie pour la colonne. Si aucune graine n'a été définie, la valeur par défaut 1 est utilisée. Pour conserver le compteur d'identité, utilisez plutôt DELETE.

Donc, ce qui suit:

DELETE FROM [MyTable];
DBCC CHECKIDENT ('[MyTable]', RESEED, 0);

Devient juste:

TRUNCATE TABLE [MyTable];

S'il vous plaît voir le TRUNCATE TABLE documentation (liée ci-dessus) pour des informations supplémentaires sur les restrictions, etc.


66
2017-12-05 18:15



j'ai essayé @anil shahs répondre et il réinitialise l'identité. Mais quand une nouvelle ligne a été insérée, il a eu le identity = 2. Donc, à la place, j'ai changé la syntaxe pour:

DELETE FROM [TestTable]

DBCC CHECKIDENT ('[TestTable]', RESEED, 0)
GO

Alors la première rangée aura l'identité = 1.


54
2017-09-11 09:34



Bien que la plupart des réponses suggèrent RESEED à 0, mais beaucoup de fois nous avons besoin de juste reseed à l'ID suivant disponible

declare @max int
select @max=max([Id])from [TestTable]
if @max IS NUll   //check when max is returned as null
  SET @max = 0
DBCC CHECKIDENT ('[TestTable]', RESEED,@max)

Cela vérifiera la table et réinitialisera à l'ID suivant.


53
2018-05-07 20:36



Bien que la plupart des réponses suggèrent RESEED à 0, et alors que certains voient cela comme un défaut TRUNCATED tables, Microsoft a une solution qui exclut le ID

DBCC CHECKIDENT ('[TestTable]', RESEED)

Cela va vérifier la table et réinitialiser à la prochaine ID. Cela est disponible depuis MS SQL 2005 à jour.

https://msdn.microsoft.com/en-us/library/ms176057.aspx


13
2017-08-11 09:47



Pour fournir explicitement une valeur pour la colonne d'identité

  1. Premier tour d'insertion d'identité - SET Identity_Insert tblPerson ON
  2. Dans la requête d'insertion, spécifiez la liste des colonnes Insert into tblPerson(PersonId, Name) values(2, 'John')

Après, vous avez les lacunes dans la colonne d'identité remplie, et si vous souhaitez que le serveur SQL calcule la valeur, désactivez Identity_Insert.

SET Identity_Insert tblPerson OFF

=======================

Si vous avez supprimé toutes les lignes d'une table et que vous souhaitez réinitialiser la valeur de la colonne d'identité.

USe commande DBCC CHECKIDENT.

DBCC CHECKIDENT(tblPerson, RESEED, 0)

Cette commande réinitialisera la colonne d'identité PersonId.


8
2017-10-28 04:53



C'est une question courante et la réponse est toujours la même: ne le faites pas. Les valeurs d'identité doivent être traitées comme arbitraires et, en tant que telles, il n'y a pas d'ordre "correct".


5
2018-02-17 15:00



@Jacob

DBCC CHECKIDENT ('[TestTable]', RESEED,0)
DBCC CHECKIDENT ('[TestTable]', RESEED)

Travaillé pour moi, je devais juste effacer toutes les entrées de la table, puis ajouté ce qui précède dans un point de déclenchement après la suppression. Maintenant, chaque fois que je supprime une entrée est prise à partir de là.


5
2017-12-06 11:46



émission 2 commande peut faire l'affaire

DBCC CHECKIDENT ('[TestTable]', RESEED,0)
DBCC CHECKIDENT ('[TestTable]', RESEED)

le premier réinitialise l'identité à zéro, et le prochain le mettra à la prochaine valeur disponible  -- Jacob


4
2017-07-12 07:57



Tronquer la table est préférable car elle efface les enregistrements, réinitialise le compteur et récupère l'espace Dis.

Delete et CheckIdent ne doivent être utilisés que lorsque les clés étrangères vous empêchent de tronquer


3
2018-04-19 16:31