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

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

Où 0 est identity valeur de Départ

Il convient de noter que si tous les {[10] } des données sont supprimés de la table via la DELETE (c'est-à-dire aucune clause WHERE), alors tant que a) les autorisations le permettent, et b) il n'y a pas de FKs référençant la table (ce qui semble être le cas ici), l'utilisation de TRUNCATE TABLE serait préférable DELETE et réinitialise la graine IDENTITY en même temps. Les détails suivants sont extraits de la page MSDN pour TRUNCATE TABLE :

Par rapport à la Supprimer l'instruction, tronquer la TABLE a les avantages suivants:

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

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

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

  Lorsque le 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 page reste dans le tableau.

  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 la suppression l'opération 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 seront 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 supprimer plutôt.

Donc ce qui suit:

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

Devient juste:

TRUNCATE TABLE [MyTable];

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

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

DELETE FROM [TestTable]

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

Alors la première ligne obtiendra l'identité = 1.

Bien que la plupart des réponses suggèrent de RESEED à 0, mais plusieurs fois nous devons simplement reseed à Next Id available

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.

Bien que la plupart des réponses suggèrent RESEED à 0, et alors que certains voient cela comme un défaut pour les tables TRUNCATED, 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 à courant.

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

Pour fournir explicitement une valeur pour la colonne d'IDENTITÉ

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

Après, vous avez rempli les lacunes dans la colonne 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é.

Utiliser DBCC Commande CHECKIDENT.

DBCC CHECKIDENT(tblPerson, RESEED, 0)

Cette commande réinitialise la colonne PersonId identity.

@jacob

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

A fonctionné pour moi, j'ai juste dû 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à.

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".

L'émission de la commande 2 peut faire l'affaire

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

La première réinitialise l'identité à zéro, et la suivante la définit sur la prochaine valeur Disponible -- jacob

Truncate table est préféré car il efface les enregistrements, réinitialise le compteur et récupère l'espace dis.

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

Réinitialiser la colonne d'identité avec un nouvel id...

DECLARE @MAX INT
SELECT @MAX=ISNULL(MAX(Id),0) FROM [TestTable]

DBCC CHECKIDENT ('[TestTable]', RESEED,@MAX)

Exécutez ce script pour réinitialiser la colonne identité. Vous devrez faire deux changements. Remplacez tableXYZ par n'importe quelle table que vous devez mettre à jour. En outre, le nom de la colonne d'IDENTITÉ doit être supprimé de la table temporaire. C'était instantané sur une table avec 35 000 lignes et 3 colonnes. Évidemment, sauvegardez la table et essayez d'abord cela dans un environnement de test.

select * 
into #temp
From tableXYZ

set identity_insert tableXYZ ON

truncate table tableXYZ

alter table #temp drop column (nameOfIdentityColumn)

set identity_insert tableXYZ OFF

insert into tableXYZ
select * from #temp

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'

Je revisite ma réponse. Je suis tombé sur un comportement étrange dans sql server 2008 r2 que vous devriez connaître.

drop table test01

create table test01 (Id int identity(1,1), descr nvarchar(10))

execute pResetIdentityField 'dbo', 'test01'

insert into test01 (descr) values('Item 1')

select * from test01

delete from test01

execute pResetIdentityField 'dbo', 'test01'

insert into test01 (descr) values('Item 1')

select * from test01

La première sélection produit 0, Item 1.

Le deuxième produit 1, Item 1. Si vous exécutez la réinitialisation juste après la création de la table, la valeur suivante est 0. Honnêtement, Je ne suis pas surpris que Microsoft ne puisse pas obtenir ce genre de choses correctement. Je l'ai découvert parce que j'ai un fichier de script qui remplit des tables de référence que je parfois exécuter après avoir recréé des tables et parfois lorsque les tables sont déjà créées.

DBCC CHECKIDENT (<TableName>, reseed, 0)

Cela définira la valeur d'identité actuelle sur 0.

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

Pour une suppression complète des lignes et réinitialiser le nombre D'identité, j'utilise cette (SQL Server 2008 R2)

USE mydb

-- ##################################################################################################################
-- DANGEROUS!!!! USE WITH CARE
-- ##################################################################################################################

DECLARE
  db_cursor CURSOR FOR
    SELECT TABLE_NAME
      FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_TYPE = 'BASE TABLE'
       AND TABLE_CATALOG = 'mydb'

DECLARE @tblname VARCHAR(50)
SET @tblname = ''

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tblname

WHILE @@FETCH_STATUS = 0
BEGIN
  IF CHARINDEX('mycommonwordforalltablesIwanttodothisto', @tblname) > 0
    BEGIN
      EXEC('DELETE FROM ' + @tblname)
      DBCC CHECKIDENT (@tblname, RESEED, 0)
    END

  FETCH NEXT FROM db_cursor INTO @tblname
END

CLOSE db_cursor
DEALLOCATE db_cursor
GO

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 non plus d'espace journal.

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

Dans votre cas seulement reconstruisez l'index et ne vous inquiétez pas de perdre le la série de l'identité comme c'est d'un commun scénario.

Première : La Spécification Identité Simplement : "Non" >> Enregistrer La Base De Données Exécution Du Projet

Après : la Spécification Identité Simplement : "OUI" >> Enregistrer la Base de données Exécution du Projet

VOTRE ID de base de données, PK commence à partir de 1 > >