Automatische waarden genereren

  • 3 minuten

Mogelijk moet u automatisch sequentiële waarden genereren voor één kolom in een specifieke tabel. Transact-SQL biedt twee manieren om dit te doen: gebruik de eigenschap IDENTITY met een specifieke kolom in een tabel of definieer een SEQUENCE-object en gebruik waarden die door dat object worden gegenereerd.

De eigenschap IDENTITY

Als u de eigenschap IDENTITY wilt gebruiken, definieert u een kolom met een numeriek gegevenstype met een schaal van 0 (alleen gehele getallen) en neemt u het sleutelwoord IDENTITY op. De toegestane typen omvatten alle typen gehele getallen en decimale typen, waarbij u expliciet een schaal van 0 geeft.

U kunt ook een optioneel seed (beginwaarde) en een verhoging (stapwaarde) opgeven. Als u het zaad en de verhoging weglaat, worden ze beide ingesteld op 1.

Notitie

De eigenschap IDENTITY wordt opgegeven in plaats van NULL of NOT NULL op te geven in de kolomdefinitie. Elke kolom met de eigenschap IDENTITY kan automatisch niet null worden. U kunt NOT NULL alleen opgeven voor zelfdocumentatie, maar als u de kolom opgeeft als NULL (wat null is), genereert de instructie voor het maken van tabellen een fout.

In slechts één kolom in een tabel kan de eigenschap IDENTITY zijn ingesteld. het wordt vaak gebruikt als de PRIMAIRE SLEUTEL of een alternatieve sleutel.

De volgende code toont het maken van de tabel Sales.Promotion die in de vorige sectievoorbeelden wordt gebruikt, maar deze keer met een identiteitskolom met de naam PromotionID als primaire sleutel:

CREATE TABLE Sales.Promotion
(
PromotionID int IDENTITY PRIMARY KEY,
PromotionName varchar(20),
StartDate datetime NOT NULL DEFAULT GETDATE(),
ProductModelID int NOT NULL REFERENCES Production.ProductModel(ProductModelID),
Discount decimal(4,2) NOT NULL,
Notes nvarchar(max) NULL
);

Notitie

De volledige details van de INSTRUCTIE CREATE TABLE vallen buiten het bereik van deze module.

Gegevens invoegen in een identiteitskolom

Wanneer de eigenschap IDENTITY is gedefinieerd voor een kolom, geven INSERT-instructies in de tabel doorgaans geen waarde op voor de kolom IDENTITY. De database-engine genereert een waarde met behulp van de volgende beschikbare waarde voor de kolom.

U kunt bijvoorbeeld een rij invoegen in de tabel Sales.Promotion zonder een waarde op te geven voor de kolom PromotionID:

INSERT INTO Sales.Promotion
VALUES
('Clearance Sale', '01/01/2021', 23, 0.10, '10% discount')

U ziet dat hoewel de component VALUES geen waarde bevat voor de kolom PromotionID , u geen kolomlijst hoeft op te geven in de INSERT-component - Identiteitskolommen zijn uitgesloten van deze vereiste.

Als deze rij de eerste is die in de tabel is ingevoegd, is het resultaat een nieuwe rij zoals deze:

PromotionID

PromotionName

StartDate

ProductModelID

Korting

Opmerkingen

1

Klaringsverkoop

2021-01-01T00:00:00

23

0,1

10% korting

Toen de tabel werd gemaakt, zijn er geen seed- of increment-waarden ingesteld voor de kolom IDENTITY, dus de eerste rij wordt ingevoegd met de waarde 1. Aan de volgende rij die moet worden ingevoegd, wordt een PromotionID-waarde van 2 toegewezen, enzovoort.

Een identiteitswaarde ophalen

Gebruik de functie SCOPE_IDENTITY om de laatst toegewezen ID-waarde binnen dezelfde sessie en hetzelfde bereik te retourneren; als volgt:

SELECT SCOPE_IDENTITY();

De functie SCOPE_IDENTITY retourneert de meest recente identiteitswaarde die is gegenereerd in het huidige bereik voor elke tabel. Als u de meest recente identiteitswaarde in een specifieke tabel nodig hebt, kunt u de functie IDENT_CURRENT gebruiken, zoals deze:

SELECT IDENT_CURRENT('Sales.Promotion');

Identiteitswaarden overschrijven

Als u de automatisch gegenereerde waarde wilt overschrijven en een specifieke waarde wilt toewijzen aan de kolom IDENTITY, moet u eerst identiteitsinvoegingen inschakelen met behulp van de instructie SET IDENTITY INSERT table_name ON. Als deze optie is ingeschakeld, kunt u een expliciete waarde invoegen voor de identiteitskolom, net als elke andere kolom. Wanneer u klaar bent, kunt u de instructie SET IDENTITY INSERT table_name OFF gebruiken om de automatische identiteitswaarden te hervatten met behulp van de laatste waarde die u expliciet hebt ingevoerd als seed.

SET IDENTITY_INSERT SalesLT.Promotion ON;

INSERT INTO SalesLT.Promotion (PromotionID, PromotionName, ProductModelID, Discount)
VALUES
(20, 'Another short sale',37, 0.3);

SET IDENTITY_INSERT SalesLT.Promotion OFF;

Zoals u hebt geleerd, wordt de eigenschap IDENTITY gebruikt om een reeks waarden voor een kolom in een tabel te genereren. De eigenschap IDENTITY is echter niet geschikt voor het coördineren van waarden in meerdere tabellen in een database. Stel dat uw organisatie onderscheid maakt tussen directe verkoop en verkoop aan resellers en gegevens voor deze verkoop in afzonderlijke tabellen wil opslaan. Beide soorten verkoop hebben mogelijk een uniek factuurnummer nodig en u kunt voorkomen dat dezelfde waarde voor twee verschillende soorten verkoop wordt gedupliceerd. Een oplossing voor deze vereiste is het onderhouden van een pool met unieke opeenvolgende waarden in beide tabellen.

Een identiteitskolom opnieuw verzenden

Af en toe moet u identiteitswaarden voor de kolom opnieuw instellen of overslaan. Hiervoor wordt de kolom 'opnieuw verzonden' met behulp van de functie DBCC CHECKIDENT. U kunt dit gebruiken om veel waarden over te slaan of om de volgende identiteitswaarde opnieuw in te stellen op 1 nadat u alle rijen in de tabel hebt verwijderd. Zie de referentiedocumentatie van Transact-SQL voor meer informatie over het gebruik van DBCC CHECKIDENT.

VOLGORDE

In Transact-SQL kunt u een reeksobject gebruiken om nieuwe opeenvolgende waarden onafhankelijk van een specifieke tabel te definiëren. Er wordt een reeksobject gemaakt met de instructie CREATE SEQUENCE, optioneel het gegevenstype op te geven (moet een geheel getal of een decimaal getal of numeriek zijn met een schaal van 0), de beginwaarde, een incrementele waarde, een maximumwaarde en andere opties met betrekking tot prestaties.

CREATE SEQUENCE Sales.InvoiceNumber AS INT
START WITH 1000 INCREMENT BY 1;

Als u de volgende beschikbare waarde uit een reeks wilt ophalen, gebruikt u de VOLGENDE WAARDE VOOR constructie, zoals deze:

INSERT INTO Sales.ResellerInvoice
VALUES
(NEXT VALUE FOR Sales.InvoiceNumber, 2, GETDATE(), 'PO12345', 107.99);

IDENTITEIT OF REEKS

Houd rekening met de volgende punten bij het bepalen of u IDENTITY-kolommen of een REEKS-object wilt gebruiken voor het automatisch invullen van waarden:

  • Gebruik REEKS als voor uw toepassing één reeks getallen tussen meerdere tabellen of meerdere kolommen in een tabel moet worden gedeeld.

  • Met SEQUENCE kunt u de waarden sorteren op een andere kolom. De NEXT VALUE FOR-constructie kan de OVER-component gebruiken om de sorteerkolom op te geven. De OVER-component garandeert dat de geretourneerde waarden worden gegenereerd in de volgorde van de ORDER BY-component van de OVER-component. Met deze functionaliteit kunt u ook rijnummers genereren voor rijen wanneer ze worden geretourneerd in een SELECT. In het volgende voorbeeld wordt de tabel Production.Product gesorteerd op de kolom Name en is de eerste geretourneerde kolom een volgnummer.

    SELECT NEXT VALUE FOR dbo.Sequence OVER (ORDER BY Name) AS NextID,
    ProductID,
    Name
FROM Production.Product;

    Hoewel de vorige instructie alleen reekswaarden selecteerde om weer te geven, worden de waarden nog steeds 'gebruikt' en zijn de weergegeven REEKS-waarden niet meer beschikbaar. Als u de bovenstaande SELECT meerdere keren uitvoert, krijgt u elke keer verschillende REEKS-waarden.

  • Gebruik SEQUENCE als voor uw toepassing meerdere nummers tegelijk moeten worden toegewezen. Een toepassing moet bijvoorbeeld vijf opeenvolgende getallen reserveren. Het aanvragen van identiteitswaarden kan leiden tot hiaten in de reeks als er gelijktijdig andere processen zijn uitgegeven. U kunt de sp_sequence_get_range systeemprocedure gebruiken om meerdere getallen in de reeks tegelijk op te halen.

  • MET SEQUENCE kunt u de specificatie van de reeks wijzigen, zoals de incrementele waarde.

  • IDENTITEITSwaarden worden beveiligd tegen updates. Als u probeert een kolom bij te werken met de eigenschap IDENTITY, krijgt u een foutmelding.