Generer automatiske verdier

Du må kanskje automatisk generere sekvensielle verdier for én kolonne i en bestemt tabell. Transact-SQL inneholder to måter å gjøre dette på: Bruk IDENTITY-egenskapen med en bestemt kolonne i en tabell, eller definer et SEKVENS-objekt og bruk verdier som genereres av objektet.

IDENTITY-egenskapen

Hvis du vil bruke IDENTITY-egenskapen, definerer du en kolonne ved hjelp av en numerisk datatype med en skala på 0 (betyr bare hele tall) og inkluderer NØKKEL-nøkkelordet. De tillatte typene inkluderer alle heltallstyper og desimaltyper der du eksplisitt gir en skala på 0.

Et valgfritt frø (startverdi) og en økning (trinnverdi) kan også angis. Utelate frø og intervaller vil sette dem begge til 1.

Bare én kolonne i en tabell kan ha identitetsegenskapen angitt. den brukes ofte som enten PRIMÆRNØKKEL eller en alternativ nøkkel.

Følgende kode viser opprettingen av tabellen Sales.Promotion som ble brukt i eksemplene for forrige del, men denne gangen med en identitetskolonne kalt PromotionID som primærnøkkel:

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

Sette inn data i en identitetskolonne

Når IDENTITY-egenskapen er definert for en kolonne, angir ikke INSERT-setninger i tabellen vanligvis en verdi for IDENTITY-kolonnen. Databasemotoren genererer en verdi ved hjelp av den neste tilgjengelige verdien for kolonnen.

Du kan for eksempel sette inn en rad i tabellen Sales.Promotion uten å angi en verdi for PromotionID-kolonnen :

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

Legg merke til at selv om VALUES-setningsdelen ikke inneholder en verdi for PromotionID-kolonnen , trenger du ikke å angi en kolonneliste i INSERT-setningsdelen – Identitetskolonner er unntatt fra dette kravet.

Hvis denne raden er den første som settes inn i tabellen, er resultatet en ny rad som dette:

Når tabellen ble opprettet, ble det ikke angitt noen verdier for frø eller intervaller for IDENTITY-kolonnen, så den første raden settes inn med en verdi på 1. Den neste raden som skal settes inn, tilordnes en PromotionID-verdi på 2 og så videre.

Hente en identitetsverdi

Hvis du vil returnere den sist tilordnede IDENTITET-verdien i samme økt og omfang, bruker du SCOPE_IDENTITY-funksjonen. som dette:

SELECT SCOPE_IDENTITY();

Funksjonen SCOPE_IDENTITY returnerer den nyeste identitetsverdien som genereres i gjeldende omfang for en tabell. Hvis du trenger den nyeste identitetsverdien i en bestemt tabell, kan du bruke IDENT_CURRENT-funksjonen, slik som dette:

SELECT IDENT_CURRENT('Sales.Promotion');

Overstyre identitetsverdier

Hvis du vil overstyre den automatisk genererte verdien og tilordne en bestemt verdi til IDENTITET-kolonnen, må du først aktivere identitetsinnlegg ved hjelp av SET IDENTITY INSERT-table_name ON-setningen. Når dette alternativet er aktivert, kan du sette inn en eksplisitt verdi for identitetskolonnen, akkurat som alle andre kolonner. Når du er ferdig, kan du bruke SET IDENTITY INSERT table_name OFF-setningen til å fortsette ved hjelp av automatiske identitetsverdier, ved hjelp av den siste verdien du eksplisitt skrev inn som et 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, brukes IDENTITY-egenskapen til å generere en sekvens med verdier for en kolonne i en tabell. IDENTITY-egenskapen er imidlertid ikke egnet for å koordinere verdier på tvers av flere tabeller i en database. Anta for eksempel at organisasjonen skiller mellom direkte salg og salg til forhandlere, og ønsker å lagre data for disse salgene i separate tabeller. Begge typer salg kan trenge et unikt fakturanummer, og det kan være lurt å unngå å duplisere samme verdi for to ulike typer salg. Én løsning for dette kravet er å opprettholde et utvalg med unike sekvensielle verdier på tvers av begge tabellene.

Sende en identitetskolonne på nytt

Noen ganger må du tilbakestille eller hoppe over identitetsverdier for kolonnen. Dette gjør du ved å «sende på nytt» kolonnen ved hjelp av DBCC CHECKIDENT-funksjonen. Du kan bruke denne til å hoppe over mange verdier eller tilbakestille den neste identitetsverdien til 1 etter at du har slettet alle radene i tabellen. Hvis du vil ha mer informasjon om hvordan du bruker DBCC CHECKIDENT, kan du se Transact-SQL referansedokumentasjon.

SEKVENS

I Transact-SQL kan du bruke et sekvensobjekt til å definere nye sekvensielle verdier uavhengig av en bestemt tabell. Et sekvensobjekt opprettes ved hjelp av SETNINGEN CREATE SEQUENCE, som eventuelt angir datatypen (må være en heltallstype eller desimal eller numerisk med en skala på 0), startverdien, en økningsverdi, en maksimumsverdi og andre alternativer relatert til ytelse.

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

Hvis du vil hente den neste tilgjengelige verdien fra en sekvens, bruker du NESTE VERDI FOR konstruksjon, slik som dette:

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

IDENTITET eller SEKVENS

Når du bestemmer deg for om du vil bruke IDENTITY-kolonner eller et SEKVENS-objekt for automatisk utfylling av verdier, må du huske på følgende punkter:

• Bruk SEKVENS hvis programmet krever deling av én enkelt serie med tall mellom flere tabeller eller flere kolonner i en tabell.

• SEKVENS lar deg sortere verdiene etter en annen kolonne. NEXT VALUE FOR construct kan bruke OVER-setningsdelen til å angi sorteringskolonnen. OVER-setningen garanterer at verdiene som returneres, genereres i rekkefølgen av OVER-setningsdelens ORDER BY-setningsdel. Med denne funksjonaliteten kan du også generere radnumre for rader etter hvert som de returneres i select. I eksemplet nedenfor sorteres Production.Product-tabellen etter Navn-kolonnen , og den første returnerte kolonnen er et sekvensielt tall.

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

  Selv om den forrige setningen bare valgte SEKVENS-verdier som skal vises, brukes verdiene fremdeles opp, og de viste SEKVENS-verdiene vil ikke lenger være tilgjengelige. Hvis du kjører select ovenfor flere ganger, får du forskjellige SEKVENS-verdier hver gang.

• Bruk SEKVENS hvis programmet krever at flere tall tilordnes samtidig. Et program må for eksempel reservere fem sekvensielle tall. Å be om identitetsverdier kan føre til hull i serien hvis andre prosesser ble utstedt samtidig. Du kan bruke sp_sequence_get_range systemprosedyre til å hente flere tall i sekvensen samtidig.

• SEKVENS lar deg endre spesifikasjonen for sekvensen, for eksempel intervallerverdien.

• IDENTITETsverdier er beskyttet mot oppdateringer. Hvis du prøver å oppdatere en kolonne med IDENTITY-egenskapen, får du en feilmelding.