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




sql server track data changes (12)

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 puisque je dois supprimer certains enregistrements de la table, le germe d'identité pour ces tables sera perturbé 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 afin que la colonne ait une séquence dans l'ordre numérique ascendant?

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


é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


@Jacob

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

Travaillé pour moi, je devais juste effacer toutes les entrées d'abord 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à.


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 va vérifier la table et réinitialiser à l'ID suivant.


C'est une question commune 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".


Il est à noter que SI toutes les données sont supprimées de la table via la clause DELETE (c'est-à-dire non WHERE ), alors a) les autorisations le permettent, et b) il n'y a pas de FK référençant la table (qui apparaît pour être le cas ici), l'utilisation de TRUNCATE TABLE serait préférable car elle fait un DELETE plus efficace et réinitialise la graine IDENTITY en même temps. Les détails suivants sont tirés de la page MSDN pour TRUNCATE TABLE :

Comparé à 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 table et enregistre uniquement les désallocations de 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 la page mais pas chaque ligne.

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

    Après l'exécution d'une instruction DELETE, la table peut toujours contenir des pages vides. Par exemple, les pages vides dans 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 désallouées rapidement 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];

Veuillez consulter la documentation de TRUNCATE TABLE (ci-dessus) pour plus d'informations sur les restrictions, etc.


Il est toujours préférable d'utiliser TRUNCATE lorsque cela est possible au lieu de supprimer tous les enregistrements car il n'utilise pas d'espace de journalisation.

Dans le cas où nous avons besoin de supprimer et de réinitialiser la graine, souvenez-vous toujours que si la table n'a jamais été remplie et que vous avez utilisé DBCC CHECKIDENT('tablenem',RESEED,0) alors le premier enregistrement aura identité = 0 comme indiqué dans la MSDN

Dans votre cas, ne reconstruisez que l'index et ne vous inquiétez pas de perdre la série d'identité car c'est un scénario courant.


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

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

Après, vous avez rempli les espaces dans la colonne d'identité, et si vous souhaitez que SQL Server 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é.

Utilisez la commande DBCC CHECKIDENT.

DBCC CHECKIDENT(tblPerson, RESEED, 0)

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


Première: Spécification d'identité Juste: "Non" >> Enregistrer la base de données Exécuter le projet

Après cela: Spécification d'identité juste: "OUI" >> Enregistrer la base de données Exécuter le projet

Votre identifiant de base de données, PK Start from 1 >>


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


Utilisez cette procédure stockée:

IF (object_id('[dbo].[pResetIdentityField]') IS NULL)
  BEGIN
    EXEC('CREATE PROCEDURE [dbo].[pResetIdentityField] AS SELECT 1 FROM DUMMY');
  END
GO

SET  ANSI_NULLS ON
GO
SET  QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[pResetIdentityField]
  @pSchemaName NVARCHAR(1000)
, @pTableName NVARCHAR(1000) AS
DECLARE @max   INT;
DECLARE @fullTableName   NVARCHAR(2000) = @pSchemaName + '.' + @pTableName;

DECLARE @identityColumn   NVARCHAR(1000);

SELECT @identityColumn = c.[name]
FROM sys.tables t
     INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
     INNER JOIN sys.columns c ON c.[object_id] = t.[object_id]
WHERE     c.is_identity = 1
      AND t.name = @pTableName
      AND s.[name] = @pSchemaName

IF @identityColumn IS NULL
  BEGIN
    RAISERROR(
      'One of the following is true: 1. the table you specified doesn''t have an identity field, 2. you specified an invalid schema, 3. you specified an invalid table'
    , 16
    , 1);
    RETURN;
  END;

DECLARE @sqlString   NVARCHAR(MAX) = N'SELECT @maxOut = max(' + @identityColumn + ') FROM ' + @fullTableName;

EXECUTE sp_executesql @stmt = @sqlString, @params = N'@maxOut int OUTPUT', @maxOut = @max OUTPUT

IF @max IS NULL
  SET @max = 0

print(@max)

DBCC CHECKIDENT (@fullTableName, RESEED, @max)
go

--exec pResetIdentityField 'dbo', 'Table'

DBCC CHECKIDENT (<TableName>, reseed, 0)

Cela va définir la valeur d'identité actuelle à 0.

Lors de l'insertion de la valeur suivante, la valeur d'identité est incrémentée à 1.


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

Où 0 est l' identity Valeur de départ





azure-sql-database