Generera automatiska värden

  • 3 minuter

Du kan behöva generera sekventiella värden automatiskt för en kolumn i en specifik tabell. Transact-SQL tillhandahåller två sätt att göra detta: använd egenskapen IDENTITY med en specifik kolumn i en tabell eller definiera ett SEQUENCE-objekt och använda värden som genereras av objektet.

Identitetsegenskapen

Om du vill använda egenskapen IDENTITY definierar du en kolumn med hjälp av en numerisk datatyp med en skala på 0 (endast heltal) och inkluderar nyckelordet IDENTITY. De tillåtna typerna innehåller alla heltalstyper och decimaltyper där du uttryckligen ger en skala på 0.

Ett valfritt frö (startvärde) och ett stegvärde kan också anges. Om du utelämnar fröet och ökar anges båda till 1.

Kommentar

Egenskapen IDENTITY anges i stället för att ange NULL eller NOT NULL i kolumndefinitionen. Alla kolumner med egenskapen IDENTITY kan inte automatiskt ogiltigförklaras. Du kan ange INTE NULL bara för självdokumentation, men om du anger kolumnen som NULL (vilket betyder nullbar) genererar instruktionen för tabellskapande ett fel.

Endast en kolumn i en tabell kan ha egenskapen IDENTITY angivet. den används ofta som primärnyckel eller en alternativ nyckel.

Följande kod visar skapandet av tabellen Sales.Promotion som användes i föregående avsnittsexempel, men den här gången med en identitetskolumn med namnet PromotionID som primärnyckel:

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
);

Kommentar

Den fullständiga informationen om CREATE TABLE-instruktionen ligger utanför omfånget för den här modulen.

Infoga data i en identitetskolumn

När egenskapen IDENTITY definieras för en kolumn anger INSERT-instruktioner i tabellen vanligtvis inte något värde för kolumnen IDENTITET. Databasmotorn genererar ett värde med hjälp av nästa tillgängliga värde för kolumnen.

Du kan till exempel infoga en rad i tabellen Sales.Promotion utan att ange ett värde för kolumnen PromotionID :

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

Observera att även om VALUES-satsen inte innehåller något värde för kolumnen PromotionID behöver du inte ange en kolumnlista i INSERT-satsen – Identitetskolumner är undantagna från det här kravet.

Om den här raden är den första som infogas i tabellen blir resultatet en ny rad som den här:

PromotionID

PromotionName

StartDate

ProductModelID

Discount

Anteckningar

1

Klareringsförsäljning

2021-01-01T00:00:00

23

0,1

10 % rabatt

När tabellen skapades angavs inga start- eller inkrementsvärden för kolumnen IDENTITY, så den första raden infogas med värdet 1. Nästa rad som ska infogas tilldelas ett PromotionID-värde på 2 och så vidare.

Hämtar ett identitetsvärde

Om du vill returnera det senast tilldelade IDENTITY-värdet inom samma session och omfång använder du funktionen SCOPE_IDENTITY. Gillar det här:

SELECT SCOPE_IDENTITY();

Funktionen SCOPE_IDENTITY returnerar det senaste identitetsvärdet som genererats i det aktuella omfånget för en tabell. Om du behöver det senaste identitetsvärdet i en specifik tabell kan du använda funktionen IDENT_CURRENT, så här:

SELECT IDENT_CURRENT('Sales.Promotion');

Åsidosätta identitetsvärden

Om du vill åsidosätta det automatiskt genererade värdet och tilldela ett specifikt värde till kolumnen IDENTITET måste du först aktivera identitetsinfogningar med hjälp av SET IDENTITY INSERT-table_name ON-instruktionen. Med det här alternativet aktiverat kan du infoga ett explicit värde för identitetskolumnen, precis som andra kolumner. När du är klar kan du använda SET IDENTITY INSERT-table_name OFF-instruktionen för att återuppta med hjälp av automatiska identitetsvärden med det sista värde som du uttryckligen angav som ett startvärde.

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;

Som du har lärt dig används egenskapen IDENTITY för att generera en sekvens med värden för en kolumn i en tabell. Egenskapen IDENTITY är dock inte lämplig för att samordna värden mellan flera tabeller i en databas. Anta till exempel att din organisation skiljer mellan direktförsäljning och försäljning till återförsäljare och vill lagra data för dessa försäljningar i separata tabeller. Båda typerna av försäljning kan behöva ett unikt fakturanummer, och du kanske vill undvika att duplicera samma värde för två olika typer av försäljning. En lösning för det här kravet är att underhålla en pool med unika sekventiella värden i båda tabellerna.

Återställer en identitetskolumn

Ibland måste du återställa eller hoppa över identitetsvärden för kolumnen. Det gör du genom att "återställa" kolumnen med hjälp av funktionen DBCC CHECKIDENT. Du kan använda detta för att hoppa över många värden eller återställa nästa identitetsvärde till 1 när du har tagit bort alla rader i tabellen. Fullständig information om hur du använder DBCC CHECKIDENT finns i referensdokumentationen för Transact-SQL.

SEKVENS

I Transact-SQL kan du använda ett sekvensobjekt för att definiera nya sekventiella värden oberoende av en specifik tabell. Ett sekvensobjekt skapas med instruktionen CREATE SEQUENCE( CREATE SEQUENCE), som eventuellt anger datatypen (måste vara en heltalstyp eller decimal eller numerisk med en skala på 0), startvärdet, ett inkrementsvärde, ett maximalt värde och andra alternativ som rör prestanda.

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

Om du vill hämta nästa tillgängliga värde från en sekvens använder du NEXT VALUE FOR-konstruktionen, så här:

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

IDENTITET eller SEKVENS

Tänk på följande när du bestämmer dig för att använda IDENTITY-kolumner eller ett SEQUENCE-objekt för automatisk ifyllning av värden:

  • Använd SEQUENCE om programmet kräver att du delar en enda serie tal mellan flera tabeller eller flera kolumner i en tabell.

  • MED SEQUENCE kan du sortera värdena efter en annan kolumn. NEXT VALUE FOR-konstruktionen kan använda OVER-satsen för att ange sorteringskolumnen. OVER-satsen garanterar att de värden som returneras genereras i ordningen för OVER-satsens ORDER BY-sats. Med den här funktionen kan du också generera radnummer för rader när de returneras i en SELECT. I följande exempel sorteras tabellen Production.Product efter kolumnen Namn och den första returnerade kolumnen är ett sekventiellt tal.

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

    Även om den föregående instruktionen bara valde SEKVENS-värden att visa, är värdena fortfarande "förbrukade" och de visade SEQUENCE-värdena kommer inte längre att vara tillgängliga. Om du kör SELECT ovan flera gånger får du olika SEKVENS-värden varje gång.

  • Använd SEQUENCE om programmet kräver att flera nummer tilldelas samtidigt. Ett program måste till exempel reservera fem sekventiella tal. Att begära identitetsvärden kan resultera i luckor i serien om andra processer samtidigt utfärdades tal. Du kan använda systemproceduren sp_sequence_get_range för att hämta flera tal i sekvensen samtidigt.

  • MED SEQUENCE kan du ändra specifikationen för sekvensen, till exempel inkrementsvärdet.

  • Identitetsvärden skyddas från uppdateringar. Om du försöker uppdatera en kolumn med egenskapen IDENTITY får du ett fel.