IDENTITY raktinis žodis yra SQL serverio nuosavybė. Kai lentelės stulpelis apibrėžiamas naudojant tapatybės ypatybę, jo vertė bus automatiškai sugeneruota prieauginė vertė . Šią reikšmę serveris sukuria automatiškai. Todėl negalime rankiniu būdu įvesti reikšmės į tapatybės stulpelį kaip naudotojas. Taigi, jei pažymime stulpelį kaip tapatybę, SQL serveris jį užpildys automatiškai padidindamas.

Sintaksė

Toliau pateikiama sintaksė, iliustruojanti IDENTITY nuosavybės naudojimą SQL serveryje:

 IDENTITY[(seed, increment)] 

Aukščiau pateikti sintaksės parametrai paaiškinti toliau:

Sėkla:Ji nurodo į lentelę įkeltos eilutės pradinę reikšmę. Pagal numatytuosius nustatymus jo reikšmė yra 1.Prieaugis:Tai nurodo prieauginę reikšmę, kuri pridedama prie paskutinės įkeltos eilutės tapatybės reikšmės. Pagal numatytuosius nustatymus jo reikšmė yra 1.

Supraskime šią sąvoką per paprastą pavyzdį.

Tarkime, kad turime ' Studentas “ stalo, ir mes norime Studento pažymėjimas turi būti generuojami automatiškai. Mes turime pradžios studento pažymėjimas 10 ir norite jį padidinti 1 su kiekvienu nauju ID. Šiame scenarijuje turi būti apibrėžtos šios reikšmės.

Sėkla: 10

Prieaugis: 1

 CREATE TABLE Student ( StudentID INT IDENTITY(10, 1) PRIMARY KEY NOT NULL, ) 

PASTABA: vienoje SQL serverio lentelėje leidžiamas tik vienas identifikavimo stulpelis.

SQL serverio IDENTITY pavyzdys

Supraskime, kaip galime naudoti tapatybės ypatybę lentelėje. Stulpelio tapatybės ypatybę galima nustatyti sukūrus naują lentelę arba ją sukūrus. Čia pamatysime abu atvejus su pavyzdžiais.

IDENTITY nuosavybė su nauja lentele

Šis sakinys sukurs naują lentelę su tapatybės nuosavybe nurodytoje duomenų bazėje:

 CREATE TABLE person ( PersonID INT IDENTITY(10,1) PRIMARY KEY NOT NULL, Fullname VARCHAR(100) NOT NULL, Occupation VARCHAR(50), Gender VARCHAR(10) NOT NULL ); 

Tada į šią lentelę įterpsime naują eilutę su IŠVADA sąlyga, kad pamatytumėte automatiškai sugeneruotą asmens ID:

 INSERT INTO person(Fullname, Occupation, Gender) OUTPUT inserted.PersonID VALUES('Sara Jackson', 'HR', 'Female'); 

Vykdant šią užklausą bus rodoma žemiau pateikta išvestis:

Ši išvestis rodo, kad pirmoji eilutė buvo įterpta su reikšme dešimt Asmens ID stulpelyje, kaip nurodyta lentelės apibrėžimo tapatybės stulpelyje.

Įterpkime kitą eilutę į asmenų stalas kaip nurodyta žemiau:

 INSERT INTO person(Fullname, Occupation, Gender) OUTPUT inserted.* VALUES('Mark Boucher', 'Cricketer', 'Male'), ('Josh Phillip', 'Writer', 'Male'); 

Ši užklausa grąžins šią išvestį:

Ši išvestis rodo, kad stulpelyje Asmens ID buvo įterpta antroji eilutė su reikšme 11 ir trečia eilutė su reikšme 12.

IDENTITY nuosavybė su esama lentele

Mes paaiškinsime šią sąvoką pirmiausia ištrindami aukščiau pateiktą lentelę ir sukurdami jas be tapatybės nuosavybės. Norėdami išmesti lentelę, atlikite toliau pateiktą teiginį:

 DROP TABLE person; 

Tada mes sukursime lentelę naudodami toliau pateiktą užklausą:

 CREATE TABLE person ( Fullname VARCHAR(100) NOT NULL, Occupation VARCHAR(50), Gender VARCHAR(10) NOT NULL ); 

Jei norime esamoje lentelėje pridėti naują stulpelį su tapatybės ypatybe, turime naudoti komandą ALTER. Toliau pateikta užklausa pridės asmens ID kaip tapatybės stulpelį asmenų lentelėje:

 ALTER TABLE person ADD PersonID INT IDENTITY(10,1) PRIMARY KEY NOT NULL; 

Aiškiai pridedant vertę į tapatybės stulpelį

Jei į aukščiau pateiktą lentelę įtrauksime naują eilutę, aiškiai nurodydami tapatybės stulpelio reikšmę, SQL serveris išmes klaidą. Žiūrėkite žemiau pateiktą užklausą:

 INSERT INTO person(Fullname, Occupation, Gender, PersonID) VALUES('Mary Smith', 'Business Analyst', 'Female', 13); 

Vykdant šią užklausą atsiras ši klaida:

Norėdami aiškiai įterpti tapatybės stulpelio vertę, pirmiausia turime nustatyti IDENTITY_INSERT reikšmę ON. Tada atlikite įterpimo operaciją, kad į lentelę įtrauktumėte naują eilutę, tada nustatykite IDENTITY_INSERT reikšmę IŠJUNGTA. Žiūrėkite toliau pateiktą kodo scenarijų:

 SET IDENTITY_INSERT person ON /*INSERT VALUE*/ INSERT INTO person(Fullname, Occupation, Gender, PersonID) VALUES('Mary Smith', 'Business Analyst', 'Female', 14); SET IDENTITY_INSERT person OFF SELECT * FROM person; 

IDENTITY_INSERT ON leidžia vartotojams įdėti duomenis į tapatybės stulpelius, o IDENTITY_INSERT IŠJUNGTA neleidžia jiems pridėti vertės šiame stulpelyje.

Vykdant kodo scenarijų bus rodoma žemiau esanti išvestis, kurioje pamatysime, kad asmens ID su reikšme 14 buvo sėkmingai įterptas.

IDENTITY Funkcija

„SQL Server“ teikia kai kurias tapatybės funkcijas, skirtas dirbti su lentelės IDENTITY stulpeliais. Šios tapatybės funkcijos išvardytos toliau:

1. @@IDENTITY Funkcija
2. SCOPE_IDENTITY() funkcija
3. IDENT_CURRENT Funkcija
4. IDENTITY Funkcija

Pažvelkime į IDENTITY funkcijas su keletu pavyzdžių.

@@IDENTITY Funkcija

@@IDENTITY yra sistemos apibrėžta funkcija, kuri rodo paskutinę tapatybės reikšmę (maksimali naudojama tapatybės vertė), sukurta stulpelio IDENTITY lentelėje per tą patį seansą. Šis funkcijos stulpelis grąžina tapatybės reikšmę, sugeneruotą sakinio po naujo įrašo įterpimo į lentelę. Tai grąžina a NULL reikšmę, kai vykdome užklausą, kuri nesukuria IDENTITY reikšmių. Jis visada veikia dabartinės sesijos metu. Jis negali būti naudojamas nuotoliniu būdu.

Pavyzdys

Tarkime, kad dabartinė maksimali tapatybės reikšmė asmenų lentelėje yra 13. Dabar toje pačioje sesijoje pridėsime vieną įrašą, kuris padidina tapatybės reikšmę vienu. Tada naudosime funkciją @@IDENTITY, kad gautume paskutinę tapatybės reikšmę, sukurtą toje pačioje sesijoje.

Čia yra visas kodo scenarijus:

 SELECT MAX(PersonID) AS maxidentity FROM person; INSERT INTO person(Fullname, Occupation, Gender) VALUES('Brian Lara', 'Cricket', 'Male'); SELECT @@IDENTITY; 

Vykdant scenarijų bus pateikta tokia išvestis, kurioje matysime maksimalią naudojamą tapatybės reikšmę 14.

SCOPE_IDENTITY() funkcija

SCOPE_IDENTITY() yra sistemos apibrėžta funkcija rodyti naujausią tapatybės reikšmę lentelėje pagal dabartinę taikymo sritį. Ši apimtis gali būti modulis, trigeris, funkcija arba išsaugota procedūra. Ji panaši į @@IDENTITY() funkciją, išskyrus tai, kad šios funkcijos taikymo sritis yra ribota. Funkcija SCOPE_IDENTITY grąžina NULL, jei ją vykdome prieš įterpimo operaciją, kuri generuoja vertę toje pačioje srityje.

Pavyzdys

Toliau pateiktame kode toje pačioje sesijoje naudojamos ir @@IDENTITY, ir SCOPE_IDENTITY() funkcijos. Šiame pavyzdyje pirmiausia bus rodoma paskutinė tapatybės reikšmė, tada į lentelę įterpiama viena eilutė. Tada jis vykdo abi tapatybės funkcijas.

 SELECT MAX(PersonID) AS maxid FROM person; INSERT INTO person(Fullname, Occupation, Gender) VALUES('Jennifer Winset', 'Actoress', 'Female'); SELECT SCOPE_IDENTITY(); SELECT @@IDENTITY; 

Vykdant kodą bus rodoma ta pati reikšmė dabartinėje sesijoje ir panaši apimtis. Žiūrėkite žemiau esantį išvesties vaizdą:

Dabar pavyzdyje pamatysime, kuo abi funkcijos skiriasi. Pirmiausia sukursime dvi pavadinimų lenteles darbuotojo_duomenys ir skyrius naudojant toliau pateiktą teiginį:

 CREATE TABLE employee_data ( emp_id INT IDENTITY(1, 1) PRIMARY KEY NOT NULL, fullname VARCHAR(20) NULL ) GO CREATE TABLE department ( department_id INT IDENTITY(100, 5) PRIMARY KEY, department_name VARCHAR(20) NULL ); 

Tada lentelėje darbuotojo_duomenys sukuriame INSERT trigerį. Šis aktyviklis naudojamas norint įterpti eilutę į skyrių lentelę, kai įterpiame eilutę į lentelę darbuotojas_duomenys.

Toliau pateikta užklausa sukuria aktyviklį, skirtą įterpti numatytąją reikšmę 'TAI' skyriaus lentelėje kiekvienoje įterpimo užklausoje į darbuotojų_duomenų lentelę:

kur rasti savo naršyklės nustatymus

 CREATE TRIGGER Insert_Department ON employee_data FOR INSERT AS BEGIN INSERT INTO department VALUES ('IT') END; 

Sukūrę trigerį, mes įterpsime vieną įrašą į lentelę darbuotojas_data ir pamatysime tiek @@IDENTITY, tiek SCOPE_IDENTITY() funkcijų išvestį.

 INSERT INTO employee_data VALUES ('John Mathew'); 

Vykdant užklausą į lentelę darbuotojas_duomenys bus pridėta viena eilutė ir toje pačioje sesijoje sugeneruojama tapatybės reikšmė. Kai įterpimo užklausa įvykdoma lentelėje darbuotojas_duomenys, ji automatiškai iškviečia aktyviklį, kad į skyrių lentelę būtų įtraukta viena eilutė. Tapatybės pradinė vertė yra 1 darbuotojo_duomenys ir 100 skyriaus lentelės.

Galiausiai vykdome toliau pateiktus teiginius, kuriuose rodoma funkcijos SELECT @@IDENTITY išvestis 100, o funkcijos SCOPE_IDENTITY – 1, nes jie grąžina tapatybės reikšmę tik toje pačioje srityje.

 SELECT MAX(emp_id) FROM employee_data SELECT MAX(department_id) FROM department SELECT @@IDENTITY SELECT SCOPE_IDENTITY() 

Štai rezultatas:

IDENT_CURRENT() Funkcija

IDENT_CURRENT yra sistemos apibrėžta funkcija rodyti naujausią IDENTITY reikšmę sugeneruotas tam tikrai lentelei esant bet kokiam ryšiui. Ši funkcija neatsižvelgia į SQL užklausos, kuri sukuria tapatybės vertę, apimtį. Šiai funkcijai reikalingas lentelės pavadinimas, kurio tapatybės reikšmę norime gauti.

Pavyzdys

Mes galime tai suprasti pirmiausia atidarę du ryšio langus. Pirmame lange įterpsime vieną įrašą, kuris sugeneruoja tapatybės reikšmę 15 asmenų lentelėje. Tada galime patikrinti šią tapatybės reikšmę kitame ryšio lange, kuriame matome tą pačią išvestį. Čia yra visas kodas:

 1st Connection Window INSERT INTO person(Fullname, Occupation, Gender) VALUES('John Doe', 'Engineer', 'Male'); GO SELECT MAX(PersonID) AS maxid FROM person; 2nd Connection Window SELECT MAX(PersonID) AS maxid FROM person; GO SELECT IDENT_CURRENT('person') AS identity_value; 

Vykdant aukščiau nurodytus kodus dviejuose skirtinguose languose bus rodoma ta pati tapatybės reikšmė.

IDENTITY() Funkcija

Funkcija IDENTITY() yra sistemos apibrėžta funkcija naudojamas tapatybės stulpeliui įterpti į naują lentelę . Ši funkcija skiriasi nuo IDENTITY nuosavybės, kurią naudojame su teiginiais CREATE TABLE ir ALTER TABLE. Šią funkciją galime naudoti tik sakinyje SELECT INTO, kuris naudojamas perkeliant duomenis iš vienos lentelės į kitą.

Ši sintaksė iliustruoja šios funkcijos naudojimą SQL serveryje:

 IDENTITY (data_type , seed , increment) AS column_name 

Jei šaltinio lentelėje yra stulpelis IDENTITY, lentelė, suformuota naudojant komandą SELECT INTO, paveldi jį pagal numatytuosius nustatymus. Pavyzdžiui , anksčiau sukūrėme lentelės asmenį su tapatybės stulpeliu. Tarkime, kad sukuriame naują lentelę, kuri paveldi asmenų lentelę, naudodami teiginius SELECT INTO su funkcija IDENTITY(). Tokiu atveju gausime klaidą, nes šaltinio lentelėje jau yra tapatybės stulpelis. Žiūrėkite žemiau pateiktą užklausą:

 SELECT IDENTITY(INT, 100, 2) AS NEW_ID, PersonID, Fullname, Occupation, Gender INTO person_info FROM person; 

Vykdydami aukščiau pateiktą teiginį, bus pateiktas toks klaidos pranešimas:

Sukurkime naują lentelę be tapatybės nuosavybės naudodami toliau pateiktą teiginį:

 CREATE TABLE student_data ( roll_no INT PRIMARY KEY NOT NULL, fullname VARCHAR(20) NULL ) 

Tada nukopijuokite šią lentelę naudodami teiginį SELECT INTO, įskaitant funkciją IDENTITY:

 SELECT IDENTITY(INT, 100, 1) AS student_id, roll_no, fullname INTO temp_data FROM student_data; 

Kai pareiškimas įvykdomas, galime jį patikrinti naudodami sp_help komanda, rodanti lentelės ypatybes.

Stulpelį IDENTITY galite pamatyti stulpelyje GUNDOMAS savybes pagal nurodytas sąlygas.

Jei naudosime šią funkciją su SELECT sakiniu, SQL serveris parodys šį klaidos pranešimą:

177 pranešimas, 15 lygis, 1 būsena, 2 eilutė Funkciją IDENTITY galima naudoti tik tada, kai SELECT sakinyje yra INTO sąlyga.

Pakartotinai naudojamos IDENTITY reikšmės

Negalime pakartotinai naudoti tapatybės reikšmių SQL serverio lentelėje. Kai ištrinsime bet kurią tapatybės stulpelio lentelės eilutę, tapatybės stulpelyje bus sukurta spraga. Be to, SQL serveris sukurs spragą, kai į tapatybės stulpelį įterpsime naują eilutę, o pareiškimas bus nesėkmingas arba atšauktas. Tarpas rodo, kad tapatybės reikšmės yra prarastos ir jų negalima vėl sugeneruoti stulpelyje IDENTITY.

Apsvarstykite toliau pateiktą pavyzdį, kad suprastumėte jį praktiškai. Jau turime asmenų lentelę su šiais duomenimis:

Tada sukursime dar dvi lenteles pavadinimu 'pozicija' , ir ' asmens_pozicija “, naudojant šį teiginį:

 CREATE TABLE POSITION ( PositionID INT IDENTITY (1, 1) PRIMARY KEY, Position_name VARCHAR (255) NOT NULL ); CREATE TABLE person_position ( PersonID INT, PositionID INT, PRIMARY KEY (PersonID, PositionID), FOREIGN KEY (PersonID) REFERENCES person (PersonID), FOREIGN KEY (PositionID) REFERENCES POSITION (PositionID) ); 

Toliau bandome įterpti naują įrašą į lentelę asmuo ir priskirti jiems poziciją, įtraukdami naują eilutę į lentelę asmuo_position. Tai padarysime naudodami toliau pateiktą operacijos ataskaitą:

 BEGIN TRANSACTION BEGIN TRY -- insert a new row into the person table INSERT INTO person (Fullname, Occupation, Gender) VALUES('Joan Smith', 'Manager', 'Male'); -- assign a position to a new person INSERT INTO person_position (PersonID, PositionID) VALUES(@@IDENTITY, 10); END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH IF @@TRANCOUNT > 0 COMMIT TRANSACTION; 

Aukščiau pateiktas operacijos kodo scenarijus sėkmingai vykdo pirmąjį įterpimo teiginį. Tačiau antrasis teiginys nepavyko, nes pozicijų lentelėje nebuvo pozicijos su id dešimt. Todėl visas sandoris buvo atšauktas.

Kadangi didžiausia tapatybės reikšmė asmens ID stulpelyje yra 16, pirmasis įterpimo sakinys sunaudojo tapatybės reikšmę 17, o tada operacija buvo atšaukta. Todėl, jei įterpsime kitą eilutę lentelėje Asmuo, kita tapatybės reikšmė bus 18. Vykdykite toliau pateiktą teiginį:

 INSERT INTO person(Fullname, Occupation, Gender) VALUES('Peter Drucker',' Writer', 'Female'); 

Dar kartą patikrinę asmenų lentelę matome, kad naujai pridėtame įraše yra tapatybės reikšmė 18.

Du IDENTITY stulpeliai vienoje lentelėje

Techniškai neįmanoma sukurti dviejų tapatybės stulpelių vienoje lentelėje. Jei tai padarysime, SQL serveris išmes klaidą. Peržiūrėkite šią užklausą:

 CREATE TABLE TwoIdentityTable ( ID1 INT IDENTITY (10, 1) NOT NULL, ID2 INT IDENTITY (100, 1) NOT NULL ) 

Kai vykdysime šį kodą, pamatysime šią klaidą:

Tačiau vienoje lentelėje galime sukurti du tapatybės stulpelius naudodami apskaičiuotą stulpelį. Ši užklausa sukuria lentelę su apskaičiuotu stulpeliu, kuris naudoja pradinį tapatybės stulpelį ir sumažina jį 1.

 CREATE TABLE TwoIdentityTable ( ID1 INT IDENTITY (10, 1) NOT NULL, SecondID AS 10000-ID1, Descriptions VARCHAR(60) ) 

Toliau į šią lentelę įtrauksime kai kuriuos duomenis naudodami toliau pateiktą komandą:

 INSERT INTO TwoIdentityTable (Descriptions) VALUES ('Javatpoint provides best educational tutorials'), ('www.javatpoint.com') 

Galiausiai patikriname lentelės duomenis naudodami SELECT teiginį. Jis grąžina šią išvestį:

Paveikslėlyje matome, kaip stulpelis SecondID veikia kaip antrasis tapatybės stulpelis, mažėjantis dešimčia nuo pradinės vertės 9990.

SQL serverio IDENTITY stulpelio klaidingos nuomonės

DBA vartotojas turi daug klaidingų nuomonių apie SQL serverio tapatybės stulpelius. Toliau pateikiamas dažniausiai pasitaikančių klaidingų nuomonių, susijusių su tapatybės stulpeliais, sąrašas:

IDENTITY stulpelis yra UNIKALUS: Remiantis oficialia SQL Server dokumentacija, tapatybės ypatybė negali garantuoti, kad stulpelio vertė yra unikali. Turime naudoti PIRMINĮ RAKTĄ, UNIKALUS suvaržymą arba UNIKALĮ indeksą, kad užtikrintume stulpelio unikalumą.

Stulpelis IDENTITY generuoja iš eilės einančius skaičius: Oficialioje dokumentacijoje aiškiai nurodyta, kad tapatybės stulpelyje priskirtos reikšmės gali būti prarastos dėl duomenų bazės gedimo arba serverio paleidimo iš naujo. Įterpiant gali atsirasti spragų tapatybės vertėje. Tarpas taip pat gali būti sukurtas, kai ištriname reikšmę iš lentelės arba įterpimo sakinys atšaukiamas. Vertės, kurios sukuria spragas, negali būti toliau naudojamos.

IDENTITY stulpelis negali automatiškai generuoti esamų verčių: Tapatybės stulpelis negali automatiškai generuoti esamų reikšmių, kol tapatybės ypatybė nebus iš naujo pateikta naudojant komandą DBCC CHECKIDENT. Tai leidžia mums koreguoti tapatybės nuosavybės pradinę vertę (pradinę eilutės vertę). Atlikus šią komandą, SQL Server nepatikrins naujai sukurtų reikšmių, jau esančių lentelėje, ar ne.

TAPATYBĖS stulpelio kaip PIRMINIO RAKTŲ pakanka eilutei identifikuoti: Jei pirminiame rakte yra lentelės tapatybės stulpelis be jokių kitų unikalių apribojimų, stulpelis gali saugoti pasikartojančias reikšmes ir užkirsti kelią stulpelio unikalumui. Kaip žinome, pirminis raktas negali saugoti pasikartojančių reikšmių, tačiau tapatybės stulpelis gali saugoti dublikatus; rekomenduojama nenaudoti pirminio rakto ir tapatybės savybių tame pačiame stulpelyje.

Netinkamo įrankio naudojimas tapatybės reikšmėms atgauti po įterpimo: Taip pat paplitusi klaidinga nuomonė, kad nežinome apie @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT ir IDENTITY() funkcijų skirtumus, kad tapatybės reikšmė būtų tiesiogiai įterpta iš ką tik įvykdyto teiginio.

Skirtumas tarp SEQUENCE ir IDENTITY

Automatiniams numeriams generuoti naudojame SEQUENCE ir IDENTITY. Tačiau ji turi tam tikrų skirtumų, o pagrindinis skirtumas yra tas, kad tapatybė priklauso nuo lentelės, o seka – ne. Apibendrinkime jų skirtumus lentelės pavidalu:

Išvada

Šiame straipsnyje bus pateikta išsami IDENTITY nuosavybės SQL Server apžvalga. Čia sužinojome, kaip ir kada naudojama tapatybės savybė, jos skirtingos funkcijos, klaidingi supratimai ir kuo ji skiriasi nuo sekos.