Generér automatiske værdier

  • 3 minutter

Du skal muligvis automatisk generere sekventielle værdier for én kolonne i en bestemt tabel. Transact-SQL indeholder to måder at gøre dette på: Brug egenskaben IDENTITY sammen med en bestemt kolonne i en tabel, eller definer et SEQUENCE-objekt, og brug værdier, der genereres af det pågældende objekt.

Egenskaben IDENTITY

Hvis du vil bruge egenskaben IDENTITY, skal du definere en kolonne ved hjælp af en numerisk datatype med en skala på 0 (dvs. kun heltal) og inkludere nøgleordet IDENTITY. De tilladte typer omfatter alle heltalstyper og decimaltyper, hvor du eksplicit angiver en skala på 0.

Der kan også angives en valgfri seed (startværdi) og en trinvis forøgelse (trinværdi). Hvis du udelader frøet og forøgelsen, indstilles de begge til 1.

Seddel

Egenskaben IDENTITY er angivet i stedet for at angive NULL eller NOT NULL i kolonnedefinitionen. Alle kolonner med egenskaben IDENTITY kan automatisk ikke være null. Du kan angive NOT NULL kun for selvdokumentation, men hvis du angiver kolonnen som NULL (hvilket betyder null), genereres der en fejl i sætningen til oprettelse af tabellen.

Kun én kolonne i en tabel kan have egenskaben IDENTITY angivet. den bruges ofte som enten primær nøgle eller en alternativ nøgle.

Følgende kode viser oprettelsen af tabellen Sales.Promotion , der blev brugt i de forrige afsnitseksempler, men denne gang med en identitetskolonne med navnet PromotionID som den primære nøgle:

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

Seddel

De fulde detaljer i CREATE TABLE-sætningen ligger uden for dette moduls område.

Indsætter data i en identitetskolonne

Når egenskaben IDENTITY er defineret for en kolonne, angiver INSERT-sætninger i tabellen generelt ikke en værdi for kolonnen IDENTITY. Databaseprogrammet genererer en værdi ved hjælp af den næste tilgængelige værdi for kolonnen.

Du kan f.eks. indsætte en række i tabellen Sales.Promotion uden at angive en værdi for kolonnen PromotionID :

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

Bemærk, at selvom VALUES-delsætningen ikke indeholder en værdi for kolonnen PromotionID , behøver du ikke at angive en kolonneliste i INSERT-delsætningen – Identitetskolonner er undtaget fra dette krav.

Hvis denne række er den første, der indsættes i tabellen, er resultatet en ny række som denne:

PromotionID

PromotionName

StartDate

ProductModelID

Rabat

Noter

1

Udsalg

2021-01-01T00:00:00

23

0.1

10% rabat

Da tabellen blev oprettet, blev der ikke angivet nogen seed- eller trinvise værdier for kolonnen IDENTITY, så den første række indsættes med en værdi på 1. Den næste række, der skal indsættes, tildeles værdien PromotionID på 2 osv.

Henter en identitetsværdi

Hvis du vil returnere den senest tildelte IDENTITY-værdi i den samme session og det samme område, skal du bruge funktionen SCOPE_IDENTITY. Som dette:

SELECT SCOPE_IDENTITY();

Funktionen SCOPE_IDENTITY returnerer den seneste identitetsværdi, der er genereret i det aktuelle område for en tabel. Hvis du har brug for den nyeste identitetsværdi i en bestemt tabel, kan du bruge funktionen IDENT_CURRENT, f.eks.:

SELECT IDENT_CURRENT('Sales.Promotion');

Tilsidesætter identitetsværdier

Hvis du vil tilsidesætte den automatisk genererede værdi og tildele en bestemt værdi til kolonnen IDENTITY, skal du først aktivere identitetsindsætninger ved hjælp af SÆTNINGen SET IDENTITY INSERT table_name ON. Når denne indstilling er aktiveret, kan du indsætte en eksplicit værdi for identitetskolonnen på samme måde som enhver anden kolonne. Når du er færdig, kan du bruge SÆTNINGen SET IDENTITY INSERT table_name OFF til at fortsætte med at bruge automatisk identitetsværdier ved hjælp af den sidste værdi, du eksplicit angav som frø.

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, bruges egenskaben IDENTITY til at generere en sekvens af værdier for en kolonne i en tabel. Egenskaben IDENTITY er dog ikke egnet til at koordinere værdier på tværs af flere tabeller i en database. Lad os f.eks. antage, at din organisation skelner mellem direkte salg og salg til forhandlere og ønsker at gemme data for dette salg i separate tabeller. Begge typer salg kan have brug for et entydigt fakturanummer, og du kan undgå at duplikere den samme værdi for to forskellige typer salg. En løsning til dette krav er at vedligeholde en pulje af entydige sekventielle værdier på tværs af begge tabeller.

Genseeding af en identitetskolonne

Nogle gange skal du nulstille eller springe identitetsværdier for kolonnen over. Det gør du ved at "se" kolonnen igen ved hjælp af funktionen DBCC CHECKIDENT. Du kan bruge dette til at springe mange værdier over eller nulstille den næste identitetsværdi til 1, når du har slettet alle rækkerne i tabellen. Du kan finde flere oplysninger om brug af DBCC CHECKIDENT i dokumentationen tilTransact-SQL reference.

SEKVENS

I Transact-SQL kan du bruge et sekvensobjekt til at definere nye sekventielle værdier uafhængigt af en bestemt tabel. Der oprettes et sekvensobjekt ved hjælp af sætningen CREATE SEQUENCE, der eventuelt leverer datatypen (skal være en heltalstype eller decimal eller numerisk med en skala på 0), startværdien, en trinvis værdi, en maksimumværdi og andre indstillinger, der er relateret til ydeevnen.

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

Hvis du vil hente den næste tilgængelige værdi fra en sekvens, skal du bruge NEXT VALUE FOR-konstruktionen på følgende måde:

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

IDENTITET ELLER SEKVENS

Når du beslutter, om du vil bruge IDENTITY-kolonner eller et SEQUENCE-objekt til automatisk udfyldning af værdier, skal du være opmærksom på følgende:

  • Brug SEQUENCE, hvis dit program kræver deling af en enkelt serie af tal mellem flere tabeller eller flere kolonner i en tabel.

  • SEQUENCE giver dig mulighed for at sortere værdierne efter en anden kolonne. NEXT VALUE FOR-konstruktionen kan bruge OVER-delsætningen til at angive sorteringskolonnen. OVER-delsætningen garanterer, at de returnerede værdier genereres i rækkefølgen af OVER-delsætningens ORDER BY-delsætning. Denne funktionalitet giver dig også mulighed for at generere rækkenumre for rækker, når de returneres i en SELECT. I følgende eksempel sorteres tabellen Production.Product efter kolonnen Name , og den første returnerede kolonne er et sekventielt tal.

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

    Selvom den forrige sætning blot valgte SEQUENCE-værdier, der skal vises, bruges værdierne stadig, og de viste SEQUENCE-værdier vil ikke længere være tilgængelige. Hvis du kører ovenstående SELECT flere gange, får du forskellige SEQUENCE-værdier hver gang.

  • Brug SEQUENCE, hvis programmet kræver, at der tildeles flere tal på samme tid. Et program skal f.eks. reservere fem sekventielle tal. Anmodning om identitetsværdier kan resultere i huller i serien, hvis andre processer samtidig blev udstedt numre. Du kan bruge sp_sequence_get_range systemprocedure til at hente flere tal i sekvensen på én gang.

  • SEQUENCE giver dig mulighed for at ændre specifikationen af sekvensen, f.eks. forøgelsesværdien.

  • IDENTITY-værdier er beskyttet mod opdateringer. Hvis du forsøger at opdatere en kolonne med egenskaben IDENTITY, får du vist en fejl.