Automaattisten arvojen luominen

Sinun on ehkä luotava automaattisesti peräkkäiset arvot tietyn taulukon yhden sarakkeen osalta. Transact-SQL on kaksi tapaa tehdä tämä: käyttää IDENTITY-ominaisuutta taulukon tietyssä sarakkeessa tai määrittää SEQUENCE-objekti ja käyttää kyseisen objektin luomia arvoja.

IDENTITY-ominaisuus

Jos haluat käyttää IDENTITY-ominaisuutta, määritä sarake käyttämällä numeerista tietotyyppiä, jonka asteikko on 0 (eli vain kokonaisluvut), ja sisällytä IDENTITY-avainsana. Sallitut tyypit sisältävät kaikki kokonaislukutyypit ja desimaalityypit, joissa nimenomaisesti annat asteikon 0.

Valinnainen alkuarvo (aloitusarvo) ja lisäys (vaiheen arvo) voidaan myös määrittää. Jos siemenet ja lisäys jätetään pois, molemmat saavat yhden.

Vain yhdellä taulukon sarakkeella voi olla määritettynä IDENTITY-ominaisuus. sitä käytetään usein joko PERUSAVAimena tai vaihtoehtoisena avaimena.

Seuraava koodi näyttää edellisissä osion esimerkeissä käytetyn Sales.Promotion-taulukon luonnin, mutta tällä kertaa perusavaimena on käyttäjätietosarake, jonka nimi on PromotionID :

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

Tietojen lisääminen käyttäjätietosarakkeeseen

Kun IDENTITY-ominaisuus on määritetty sarakkeelle, taulukon INSERT-lausekkeet eivät yleensä määritä ARVOA IDENTITY-sarakkeelle. Tietokantamoduuli luo arvon käyttäen sarakkeen seuraavaa käytettävissä olevaa arvoa.

Voit esimerkiksi lisätä rivin Sales.Promotion-taulukkoon määrittämättä Kampanjan tunnus -sarakkeelle arvoa:

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

Huomaa, että vaikka VALUES-lause ei sisällä PromotionID-sarakkeen arvoa, sinun ei tarvitse määrittää sarakeluetteloa INSERT-lauseessa – Käyttäjätiedot-sarakkeet on vapautettu tästä vaatimuksesta.

Jos tämä rivi on ensimmäinen taulukkoon lisätty rivi, tuloksena on seuraava uusi rivi:

Kun taulukko luotiin, KÄYTTÄJÄtiedot-sarakkeelle ei asetettu alku- tai lisäysarvoja, joten ensimmäinen rivi lisätään arvolla 1. Seuraavalle lisättävälle riville määritetään PromotionID-arvoksi 2 ja niin edelleen.

Käyttäjätietojen arvon noutaminen

Jos haluat palauttaa viimeksi määritetyn IDENTITY-arvon samassa istunnossa ja vaikutusalueella, käytä SCOPE_IDENTITY funktiota. Näin:

SELECT SCOPE_IDENTITY();

SCOPE_IDENTITY-funktio palauttaa uusimman käyttäjätietoarvon, joka on luotu minkä tahansa taulukon nykyisessä laajuudessa. Jos tarvitset uusimman käyttäjätietoarvon tiettyyn taulukkoon, voit käyttää IDENT_CURRENT-funktiota seuraavasti:

SELECT IDENT_CURRENT('Sales.Promotion');

Käyttäjätietojen arvojen ohittaminen

Jos haluat ohittaa automaattisesti luodun arvon ja antaa tietyn arvon IDENTITY-sarakkeelle, sinun on ensin otettava käyttöön käyttäjätietojen lisäykset käyttämällä SET IDENTITY INSERT -table_name ON -lauseketta. Kun tämä asetus on käytössä, voit lisätä eksplisiittisen arvon käyttäjätietosarakkeelle samalla tavalla kuin muutkin sarakkeet. Kun olet valmis, voit käyttää MÄÄRITÄ KÄYTTÄJÄtiedot - table_name OFF -lauseketta ja jatkaa automaattisten käyttäjätietojen arvojen käyttämistä käyttämällä viimeistä eksplisiittisesti syöttämääsi arvoa alkuarvona.

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;

Kuten olet oppinut, IDENTITY-ominaisuutta käytetään luomaan arvojen sarja taulukon sisäiselle sarakkeelle. IDENTITY-ominaisuus ei kuitenkaan sovellu tietokannan useiden taulukoiden arvojen koordinointiin. Oletetaan esimerkiksi, että organisaatiosi erottaa suoran myynnin ja myynnin jälleenmyyjiin ja haluaa tallentaa näiden myynnin tiedot erillisiin taulukoihin. Kummatkin myyntilajit saattavat tarvita yksilöllisen laskunumeron, ja haluat ehkä välttää saman arvon monistamista kahteen eri myyntityyppiin. Yksi ratkaisu tähän vaatimukseen on ylläpitää yksilöllisten järjestyksessä olevien arvojen varantoa molemmissa taulukoissa.

Käyttäjätietosarakkeen sisentäminen uudelleen

Joskus sinun on nollattava tai ohitettava sarakkeen käyttäjätietoarvot. Voit tehdä tämän "poistamalla" sarakkeen uudelleen käyttämällä DBCC CHECKIDENT -funktiota. Tämän avulla voit ohittaa useita arvoja tai palauttaa seuraavan käyttäjätietoarvon arvoon 1 sen jälkeen, kun olet poistanut kaikki taulukon rivit. Lisätietoja DBCC CHECKIDENT -toiminnosta on Transact-SQL viitedokumentaatiossa.

SEKVENSSI

Transact-SQL:ssä voit määrittää uudet peräkkäiset arvot jakso-objektin avulla tietystä taulukosta riippumattomasti. Jakso-objekti luodaan käyttämällä CREATE SEQUENCE -lauseketta, joka valinnaisesti antaa tietotyypin (sen on oltava kokonaislukutyyppi tai desimaali tai numeerinen, jonka asteikko on 0), aloitusarvo, lisäysarvo, enimmäisarvo ja muut suorituskykyyn liittyvät vaihtoehdot.

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

Jos haluat noutaa seuraavan käytettävissä olevan arvon järjestyksestä, käytä KONstruktorin NEXT-ARVOA seuraavasti:

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

KÄYTTÄJÄTIEDOT tai SEKVENSSI

Kun päätät, käytetäänkö KÄYTTÄJÄtiedot-sarakkeita vai SEQUENCE-objektia arvojen automaattiseen täyttämiseen, ota huomioon seuraavat asiat:

• Käytä SEQUENCE-funktiota, jos sovelluksesi edellyttää yksittäisen numerosarjan jakamista useiden taulukoiden tai useiden sarakkeiden välillä taulukon sisällä.

• SEQUENCE-funktion avulla voit lajitella arvot toisen sarakkeen mukaan. KONstruktorin NEXT VALUE voi käyttää OVER-lausetta lajittelusarakkeen määrittämiseen. OVER-lause takaa, että palautetut arvot luodaan OVER-lauseen ORDER BY -lauseen järjestyksessä. Tämän toiminnon avulla voit myös luoda rivinumeroita riveille, kun niitä palautetaan SELECT-funktiossa. Seuraavassa esimerkissä Production.Product-taulukko lajitellaan Nimi-sarakkeen mukaan ja ensimmäinen palautettu sarake on peräkkäinen luku.

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

  Vaikka edellinen lauseke oli juuri valitsemassa näytettäviä SEQUENCE-arvoja, arvoja käytetään edelleen, eivätkä näytetyt SEQUENCE-arvot ole enää käytettävissä. Jos suoritat yllä olevan SELECT-funktion useita kertoja, saat eri SEKVENSSIarvot joka kerta.

• Käytä SEQUENCE-funktiota, jos sovelluksesi edellyttää useiden lukujen määrittämista samanaikaisesti. Sovelluksen on esimerkiksi varattava viisi peräkkäistä lukua. Käyttäjätietojen arvojen pyytäminen voi aiheuttaa sarjan aukkoja, jos muille prosesseille annetaan samanaikaisesti numeroita. voit sp_sequence_get_range järjestelmän toimintosarjan avulla noutaa useita numeroita järjestyksessä kerralla.

• SEQUENCE-funktion avulla voit muuttaa jakson määritystä, kuten lisäysarvoa.

• KÄYTTÄJÄtietojen arvot on suojattu päivityksiltä. Jos yrität päivittää sarakkeen IDENTITY-ominaisuudella, saat virheilmoituksen.