88 views

Identiteitszaad resetten na het verwijderen van records in SQL Server

Hosted by Code Expert
sql

Ik heb records ingevoegd in een SQL Server-databasetabel. Voor de tabel was een primaire sleutel gedefinieerd en de identiteits-seed voor automatisch verhogen is ingesteld op “Ja”. Dit wordt voornamelijk gedaan omdat in SQL Azure voor elke tabel een primaire sleutel en identiteit moet zijn gedefinieerd.

Maar aangezien ik een aantal records uit de tabel moet verwijderen, wordt de identiteit van die tabellen verstoord en wordt de indexkolom (die automatisch wordt gegenereerd met een verhoging van 1) verstoord.

Hoe kan ik de identiteitskolom opnieuw instellen nadat ik de records heb verwijderd, zodat de kolom een oplopende numerieke volgorde heeft?

De identiteitskolom wordt nergens in de database als externe sleutel gebruikt.

Antwoord 1, autoriteit 100%

De DBCC CHECKIDENTbeheeropdracht wordt gebruikt om de identiteitsteller te resetten. De opdrachtsyntaxis is:

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

Voorbeeld:

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

Het werd niet ondersteund in eerdere versies van de Azure SQL Database, maar wordt nu wel ondersteund.

Met dank aan Solomon Rutzkyde docsvoor de opdracht zijn nu opgelost.

Antwoord 2, autoriteit 19%

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

Waar 0 is IDENTITYStartwaarde

Antwoord 3, autoriteit 9%

Hoewel de meeste antwoorden RESEED naar 0 suggereren, moeten we vaak gewoon opnieuw zaaien naar de volgende beschikbare ID

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)

Hiermee wordt de tabel gecontroleerd en teruggezet naar de volgende ID.

Antwoord 4, autoriteit 8%

Opgemerkt moet worden dat ALS allegegevens uit de tabel worden verwijderd via de DELETE(dwz geen WHERE-clausule), dan zolang a) permissies dit toelaten, en b) er geen FK’s zijn die verwijzen naar de tabel (wat hier het geval lijkt te zijn), zou het gebruik van TRUNCATE TABLEde voorkeur hebben omdat het efficiënter is DELETEenreset tegelijkertijd de IDENTITYseed. De volgende details zijn afkomstig van de MSDN-pagina voor TRUNCATE TABLE:

Vergeleken met de DELETE-instructie heeft TRUNCATE TABLE de volgende voordelen:

  • Er wordt minder transactielogruimte gebruikt.

    De DELETE-instructie verwijdert rijen één voor één en registreert een vermelding in het transactielogboek voor elke verwijderde rij. TRUNCATE TABLE verwijdert de gegevens door de toewijzing van de gegevenspagina’s die worden gebruikt om de tabelgegevens op te slaan ongedaan te maken en registreert alleen de paginadeallocaties in het transactielogboek.

  • Er worden doorgaans minder sloten gebruikt.

    Wanneer de DELETE-instructie wordt uitgevoerd met een rijvergrendeling, wordt elke rij in de tabel vergrendeld voor verwijdering. TRUNCATE TABLE vergrendelt altijd de tabel (inclusief een schema (SCH-M) lock) en pagina, maar niet elke rij.

  • Zonder uitzondering blijven er nul pagina’s in de tabel.

    Nadat een DELETE-instructie is uitgevoerd, kan de tabel nog steeds lege pagina’s bevatten. Lege pagina’s in een heap kunnen bijvoorbeeld niet ongedaan worden gemaakt zonder ten minste een exclusieve (LCK_M_X) tabelvergrendeling. Als de verwijderbewerking geen tabelvergrendeling gebruikt, zal de tabel (heap) veel lege pagina’s bevatten. Voor indexen kan de verwijderingsbewerking lege pagina’s achterlaten, hoewel deze pagina’s snel worden verwijderd door een proces voor het opschonen van de achtergrond.

Als de tabel een identiteitskolom bevat, wordt de teller voor die kolom teruggezet naar de beginwaarde die voor de kolom is gedefinieerd. Als er geen seed is gedefinieerd, wordt de standaardwaarde 1 gebruikt. Gebruik in plaats daarvan DELETE om de identiteitsteller te behouden.

Dus het volgende:

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

Wordt gewoon:

TRUNCATE TABLE [MyTable];

Zie de TRUNCATE TABLEdocumentatie (hierboven gelinkt) voor aanvullende informatie over beperkingen, enz.

Antwoord 5, autoriteit 5%

Ik probeerde @anil shahsantwoord en het resette de identiteit. Maar toen een nieuwe rij werd ingevoegd, kreeg deze de identity = 2. Dus in plaats daarvan heb ik de syntaxis gewijzigd in:

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

Dan krijgt de eerste rij de identiteit = 1.

Antwoord 6, autoriteit 2%

Hoewel de meeste antwoorden suggereren RESEEDNAAR 0, en hoewel sommige dit als een fout zien voor TRUNCATEDtabellen, heeft Microsoft dat een oplossing heeft Exclusief de ID

DBCC CHECKIDENT ('[TestTable]', RESEED)

Hiermee vinkt u de tabel en reset naar de volgende ID. Dit is beschikbaar sinds MS SQL 2005 tot stroom.

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

Antwoord 7

Uitgevende 2-opdracht kan de truc doen 

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

De eerste reset de identiteit naar nul, en de volgende zal het instellen op de volgende beschikbare waarde
– Jacob

Antwoord 8

@jacob 

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

Werkte voor mij, ik moest gewoon alle vermeldingen eerst uit de tabel wissen en voegde vervolgens het bovenstaande toe in een triggerpunt na verwijderen. Nu wanneer ik een invoer verwijdert, wordt vanaf daar genomen.

Antwoord 9

TruncateTABLE heeft de voorkeur omdat het de records wist, reset de teller en wordt de schijfruimte teruggeworpen.

Deleteen CheckIdentMoet alleen worden gebruikt waar buitenlandse sleutels u voorkomen uit het afknippen.

Antwoord 10

Reset Identity Column met nieuwe ID … 

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

Antwoord 11

Dit is een gemeenschappelijke vraag en het antwoord is altijd hetzelfde: Doe het niet. Identiteitswaarden moeten als willekeurig worden behandeld en, als zodanig, er is geen “juiste” bestelling.

Antwoord 12

Voer dit script uit om de kolom Identiteit te resetten. U moet twee wijzigingen aanbrengen. Vervang Tablexyz met elke tabel die u moet bijwerken. Ook is de naam van de identiteitskolom nodig van de Temp-tabel. Dit was onmiddellijk op een tafel met 35.000 rijen & amp; 3 kolommen. Een back-up van de tafel en probeer dit eerst in een testomgeving. 

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

Antwoord 13

Ik gebruik het volgende script om dit te doen. Er is slechts één scenario waarin het een “fout” produceert, die is als u alle rijen van de tabel hebt verwijderd, en IDENT_CURRENTis momenteel ingesteld op 1, dat wil zeggen dat er slechts één rij in de tabel was om te beginnen. 

DECLARE @maxID int = (SELECT MAX(ID) FROM dbo.Tbl)
;
IF @maxID IS NULL
    IF (SELECT IDENT_CURRENT('dbo.Tbl')) > 1
        DBCC CHECKIDENT ('dbo.Tbl', RESEED, 0)
    ELSE
        DBCC CHECKIDENT ('dbo.Tbl', RESEED, 1)
    ;
ELSE
    DBCC CHECKIDENT ('dbo.Tbl', RESEED, @maxID)
;

Antwoord 14

Ik heb geprobeerd dit voor een groot aantal tabellen te krijgen tijdens de ontwikkeling, en dit werkt als een charme. 

DBCC CHECKIDENT('www.newsType', RESEED, 1);
DBCC CHECKIDENT('www.newsType', RESEED);

Dus je dwingt het eerst om in te stellen op 1, daarna stel je het in op de hoogste index van de rijen in de tabel. Snelle en gemakkelijke rest van de idex.

Antwoord 15

DBCC CHECKIDENT (<TableName>, reseed, 0)

Hiermee wordt de huidige identiteitswaarde op 0 gezet.

Bij het invoegen van de volgende waarde wordt de identiteitswaarde verhoogd naar 1.

Antwoord 16

Gebruik deze opgeslagen procedure:

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'

Ik kijk even naar mijn antwoord. Ik kwam een vreemd gedrag tegen in sql server 2008 r2 waarvan je op de hoogte moet zijn.

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

De eerste selectie levert 0, Item 1op.

De tweede levert 1, Item 1op. Als u de reset uitvoert direct nadat de tabel is gemaakt, is de volgende waarde 0. Eerlijk gezegd verbaast het me niet dat Microsoft dit niet goed kan krijgen. Ik ontdekte het omdat ik een scriptbestand heb dat referentietabellen vult die ik soms uitvoer nadat ik tabellen opnieuw heb gemaakt en soms wanneer de tabellen al zijn gemaakt.

Antwoord 17

Voor een volledige VERWIJDERING van rijen en het resetten van de IDENTITY-telling, gebruik ik dit (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

Antwoord 18

Opnieuw zaaien naar 0 is niet erg praktisch, tenzij je de tafel als geheel aan het opruimen bent.

anders is het antwoord van Anthony Raymond perfect. Haal eerst de maximale identiteitskolom op en zaai deze vervolgens met max.

Antwoord 19

Het is altijd beter om waar mogelijk TRUNCATEte gebruiken in plaats van alle records te verwijderen, omdat het ook geen logruimte in beslag neemt.

In het geval dat we moeten verwijderen en de seed moeten resetten, onthoud dan altijd dat als de tabel nooit is gevuld en u DBCC CHECKIDENT('tablenem',RESEED,0)hebt gebruikt
dan krijgt het eerste record identiteit = 0
zoals vermeld op msdn-documentatie

In jouw geval alleen de index opnieuw opbouwenen maak je geen zorgen over het kwijtraken van de
reeks identiteiten, aangezien dit een veelvoorkomend scenario is.

Antwoord 20

Eerste: Identiteitsspecificatie Gewoon: “Nee” >> Database opslaan Project uitvoeren

Daarna: Identiteitsspecificatie Gewoon: “JA” >> Database opslaan Project uitvoeren

Uw database-ID, PK Begin bij 1 >>

Previous articleJe moet een Theme.AppCompat-thema (of afstammeling) gebruiken bij deze activiteit
Next articleLinalGError verhogen (“SVD heeft”) Linalgerror niet convergeren: SVD heeft zich niet samengevoegd in Matplotlib PCA-bepaling

Other episodes

Bestel op aflopende datum – maand, dag en jaar

sql Code Expert - 0
Dit lijkt stom, maar ik heb gewoon een lijst met te bestellen datums nodig met de meest recente datum bovenaan. Het gebruik van order...
Read more

Maak de maand en het jaar van een datetime in SQL Server 2005

sql Code Expert - 0
Ik heb de maand+jaar nodig vanaf de datetime in SQL Server zoals 'Jan 2008'. Ik groepeer de zoekopdracht per maand, jaar. Ik heb gezocht...
Read more

PostgreSQL: hoofdletterongevoelige tekenreeksvergelijking

sql Code Expert - 0
Is er een simpele negeer-case-vergelijking voor PostgreSQL? Ik wil vervangen: SELECT id, user_name FROM users ...
Read more

Kolom aan tabel toevoegen en vervolgens bijwerken in transactie

sql Code Expert - 0
Ik ben een script aan het maken dat op een MS SQL-server wordt uitgevoerd. Dit script voert meerdere instructies uit en moet transactioneel zijn....
Read more

Wanneer of waarom zou je een rechter outer join gebruiken in plaats van een left?

sql Code Expert - 0
Wikipediastelt: "In de praktijk worden expliciete rechter outer joins zelden gebruikt, omdat ze altijd kunnen worden vervangen door left outer joins en geen extra functionaliteit...
Read more

sqlite kopieer gegevens van de ene tabel naar de andere

sql Code Expert - 0
SQLITE Ik heb 2 tabellen "Bron" en "Bestemming" die dezelfde velden hebben. ID en LAND, hoewel ze allebei andere velden hebben die niet gemeenschappelijk zijn. Ik...
Read more

Een lijst met tabellen en velden in elke tabel in een database ophalen

sql Code Expert - 0
Ik ben aan het kijken naar het maken van een basis-ORM (puur voor de lol), en vroeg me af of er een manier is...
Read more

© Copyright - Code Expert 2021