Identiteitstoename springt in SQL Server-database

In een van mijn tabellen Feein kolom “ReceiptNo” in SQL Server 2012 database-identiteitstoename begon plotseling naar 100s te springen in plaats van 1 afhankelijk van de volgende twee dingen.

1. als het 1205446 is, springt het naar 1206306, als het 1206321 is, springt het naar 1207306 en als het 1207314 is, springt het naar 1208306. Ik wil u erop wijzen dat de laatste drie cijfers overblijven constant dwz 306 wanneer het springen plaatsvindt, zoals weergegeven in de volgende afbeelding.

2. dit probleem treedt op wanneer ik mijn computer opnieuw opstart

Antwoord 1, autoriteit 100%

U ondervindt dit gedrag vanwege een prestatieverbetering sinds SQL Server 2012.

Het gebruikt nu standaard een cachegrootte van 1.000 bij het toewijzen van IDENTITY-waarden voor een int-kolom en het herstarten van de service kan ongebruikte waarden “verliezen” (de cachegrootte is 10.000 voor bigint/numeric).

Dit wordt vermeld in de documentatie

SQL Server kan identiteitswaarden cachen om prestatieredenen en
sommige van de toegewezen waarden kunnen verloren gaan tijdens een databasefout of
server opnieuw opstarten. Dit kan leiden tot hiaten in de identiteitswaarde op
invoegen. Als hiaten niet acceptabel zijn, moet de applicatie zijn
eigen mechanisme om sleutelwaarden te genereren. Een sequentiegenerator gebruiken met
de optie NOCACHEkan de hiaten beperken tot transacties die nooit
toegewijd.

Uit de gegevens die je hebt laten zien, lijkt het erop dat dit gebeurde na de gegevensinvoer voor 22 december en toen SQL Server opnieuw werd opgestart, werden de waarden 1206306 - 1207305gereserveerd. Nadat de gegevensinvoer voor 24 – 25 december was gedaan, werd opnieuw opgestart en heeft SQL Server het volgende bereik 1207306 - 1208305gereserveerd dat zichtbaar is in de vermeldingen voor de 28e.

Tenzij u de service met ongebruikelijke frequentie opnieuw start, is het onwaarschijnlijk dat “verloren” waarden een significante deuk zullen veroorzaken in het waardenbereik dat door het gegevenstype wordt toegestaan, dus het beste beleid is om u er geen zorgen over te maken.

Als dit om de een of andere reden een echt probleem voor je is, zijn enkele mogelijke oplossingen…

1. Je kunt een SEQUENCEgebruiken in plaats van een identiteitskolom en definieer bijvoorbeeld een kleinere cachegrootte en gebruik NEXT VALUE FORin een kolomstandaard.
2. Of pas traceringsvlag 272 toe waardoor de IDENTITY-toewijzing wordt vastgelegd zoals in versies tot 2008 R2. Dit geldt globaal voor alle databases.
3. Of, voor recente versies, voer ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFFuit om de identiteitscaching voor een specifieke database uit te schakelen.

U dient zich ervan bewust te zijn dat geen van deze tijdelijke oplossingen ervoor zorgt dat er geen hiaten ontstaan. Dit is nooit gegarandeerd door IDENTITYomdat het alleen mogelijk zou zijn door inserts aan de tabel te serialiseren. Als u een kolom zonder tussenruimte nodig heeft, moet u een andere oplossing gebruiken dan IDENTITYof SEQUENCE

Antwoord 2, autoriteit 35%

Dit probleem treedt op na het herstarten van de SQL Server.

De oplossing is:

• Voer SQL Server Configuration Manageruit.

• Selecteer SQL Server Services.

  

• Klik met de rechtermuisknop op SQL Serveren selecteer Eigenschappen.

• Typ in het openingsvenster onder Opstartparameters-T272en klik op Toevoegenen druk vervolgens op Toepassen-knop en start opnieuw.

  

Antwoord 3, autoriteit 20%

Vanaf SQL Server 2017+kunt u WIJZIG DATABASE SCOPED CONFIGURATIE:

IDENTITY_CACHE = { AAN | UIT }

Hiermee wordt de identiteitscache op databaseniveau in- of uitgeschakeld. De standaard
staat aan. Identity caching wordt gebruikt om de INSERT-prestaties op
tabellen met Identiteitskolommen. Om hiaten in de waarden van de . te voorkomen
Identiteitskolom in gevallen waarin de server onverwacht opnieuw opstart of
failover naar een secundaire server, schakel de IDENTITY_CACHE optie uit.
Deze optie is vergelijkbaar met de bestaande SQL Server Trace Flag 272,
behalve dat het kan worden ingesteld op databaseniveau in plaats van alleen op
het serverniveau.

(…)

G. IDENTITY_CACHE instellen

Dit voorbeeld schakelt de identiteitscache uit.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;

Antwoord 4, autoriteit 15%

Ik weet dat mijn antwoord misschien te laat op het feest komt. Maar ik heb het op een andere manier opgelost door een opgeslagen opstartprocedure toe te voegen in SQL Server 2012.

Maak een volgende opgeslagen procedure in de hoofddatabase.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ResetTableNameIdentityAfterRestart]
AS
BEGIN
begin TRAN
    declare @id int = 0
    SELECT @id =  MAX(id) FROM [DatabaseName].dbo.[TableName]
    --print @id
    DBCC CHECKIDENT ('[DatabaseName].dbo.[TableName]', reseed, @id)
Commit
END

Voeg het vervolgens toe aan Opstarten met behulp van de volgende syntaxis.

EXEC sp_procoption 'ResetOrderIdentityAfterRestart', 'startup', 'on';

Dit is een goed idee als u weinig tabellen heeft. maar als je veel tabellen moet doen, werkt deze methode nog steeds, maar geen goed idee.

Antwoord 5, autoriteit 9%

Dit is nog steeds een veel voorkomend probleem bij veel ontwikkelaars en applicaties, ongeacht de grootte.

Helaas lossen de bovenstaande suggesties niet alle scenario’s op, d.w.z. gedeelde hosting, u kunt er niet op vertrouwen dat uw host de opstartparameter -t272 instelt.

Als je bestaande tabellen hebt die deze identiteitskolommen voor primaire sleutels gebruiken, is het een ENORME inspanning om die kolommen te verwijderen en nieuwe te maken om de BS-reeksoplossing te gebruiken. De Sequence-oplossing is alleen goed als u de tabellen helemaal opnieuw ontwerpt in SQL 2012+

Waar het op neerkomt is dat als je Sql Server 2008R2 gebruikt, BLIJF EROP. Serieus, blijf erbij. Totdat Microsoft toegeeft dat ze een ENORME bug hebben geïntroduceerd, die er nog steeds is, zelfs in Sql Server 2016, moeten we niet upgraden totdat ze het bezitten en het FIXEN.

Microsoft heeft meteen een baanbrekende wijziging geïntroduceerd, d.w.z. ze hebben een werkende API verbroken die niet langer werkt zoals ontworpen, omdat hun systeem hun huidige identiteit vergeet bij een herstart. Cache of geen cache, dit is onaanvaardbaar, en de Microsoft-ontwikkelaar met de naam Bryan moet het bezitten, in plaats van de wereld te vertellen dat het “door ontwerp” en een “functie” is. Natuurlijk, de caching is een functie, maar uit het oog verliezen wat de volgende identiteit zou moeten zijn, IS GEEN FUNCTIE. Het is een fricken BUG!!!

Ik zal de tijdelijke oplossing die ik heb gebruikt, delen, omdat Mijn DB’s zich op Shared Hosting-servers bevinden, en ik laat mijn Primary Key-kolommen ook niet vallen en opnieuw maken, dat zou een enorme PITA zijn.

In plaats daarvan is dit mijn schandelijke hack (maar niet zo schandelijk als deze POS-bug die Microsoft heeft geïntroduceerd).

Hacken/repareren:

Voordat u uw invoegopdrachten uitvoert, moet u uw identiteit vóór elke invoeging opnieuw invoeren. Deze oplossing wordt alleen aanbevolen als u geen beheerderscontrole heeft over uw Sql Server-instantie, anders raad ik u aan opnieuw te zaaien bij het opnieuw opstarten van de server.

declare @newId int -- where int is the datatype of your PKey or Id column
select @newId = max(YourBuggedIdColumn) from YOUR_TABLE_NAME
DBCC CheckIdent('YOUR_TABLE_NAME', RESEED, @newId)

Alleen die 3 regels direct voor je invoeging, en je zou goed moeten zijn om te gaan. Het heeft niet zoveel invloed op de prestaties, d.w.z. het zal onmerkbaar zijn.

Veel succes.

Antwoord 6, autoriteit 5%

Er zijn veel mogelijke redenen voor het springen van identiteitswaarden. Ze variëren van teruggedraaide invoegingen tot identiteitsbeheer voor replicatie. Wat dit in uw geval veroorzaakt, kan ik niet zeggen zonder enige tijd in uw systeem door te brengen.

U moet echter weten dat u in geen geval kunt aannemen dat een identiteitskolom aaneengesloten is. Er zijn gewoon te veel dingen die hiaten kunnen veroorzaken.

Je kunt hier wat meer informatie over vinden: http://sqlity.net/en/792/the-gap-in-the-identity-value-sequence/