Je, znam da sam rekao kako ću ovdje pričati o Katmai-ju i uzbudljivim još-uvijek-novim stvarima u Yukon-u.. Budem. Idući put :) Danas, nešto što mi se mota po glavi zadnjih par dana. Stara i otrcana tema, možda, ali nažalost izgleda još uvijek aktualna.
Prije nekog vremena, na Mobility Day (koji je BTW po meni sasvim uspio kao konfa, svaka čast organizatorima i predavačima!) sjedio sam na predavanju na kojem je predavač, pored još nekih "bisera" koje sad neću spominjati, ničim izazvan ustvrdio kako je ustvari dobra ideja postaviti GUID kao primary key ako se tablica ima namjeru koristiti u replikaciji. Hm..
Pukom slučajnošću, dan-dva iza toga, na hr.comp.programiranje.baze -- prilično divlje zna biti tamo ponekad, ali moj samaritanski duh se hrani pomažući ljudima gdje god -- u jednom odgovoru sam onako u prolazu savjetovao OP-u da ne koristi IDENTITY za primary key. I u tren oka par ljudi je skočilo s pitanjem "pa zašto sad to?". Hmm..
Da stvar bude još gora, jedan učesnik u threadu je, valjda iz želje da pojasni stvari, postao link na članak Kimberly Tripp o clustered indeksima. Hmmm..
OK, čemu dakle taj hm-hmm-hmmm od mene? Zaista, zašto ne koristiti autogenerated vrijednosti (kao što su IDENTITY i GUID) za primary key na tablici? Odakle pomutnja? Pokušat ću jednostavnim jezikom, i uglavnom o IDENTITY-ju, njega se puno više zloupotrebljava.
Prvi i osnovni razlog je taj što ključevi (primarni, sekundarni, kandidati, kojigod) stvar logičkog dizajna baze podataka, i tiču se samo integriteta podataka. Osnovna zadaća ključeva je da se pomoću njih može jednoznačno identificirati neki red. IDENTITY i GUID su implementacijski detalj u SQL Serveru -- oni ne postoje u realnom svijetu, nisu dio poslovne domene, nemaju ama baš nikakve veze s podacima, njihova vrijednost nije poznata do trenutka nakon što su podaci već upisani, korisniku ne znače ništa i ne može ih koristiti u upitima, GUID pored toga i nije nešto što želite ukucati preko tipkovnice, etc. Očito je da oni ne ispunjavaju onu maloprijespomenutu osnovnu zadaću - da identificiraju red u tablici.
Tu se javlja i par sasvim praktičnih problema. Prvi je taj da, kako sam već rekao, vrijednost IDENTITY kolone nije poznata prije nego što smo podatke upisali u bazu. Uzmimo za primjer onaj čuveni sa SalesOrderHeader i SalesOrderDetail tablicama iz AdventureWorks sample baze (usputbudirečeno, ta baza je više primjer kako NE dizajnirati bazu, nego obrnuto.. no dobro). Imamo SalesOrderID kolonu s dignutim IDENTITY property-jem kao PK na SalesOrderHeader tablici, i postavljen FK constraint sa SalesOrderDetail na SalesOrderHeader preko SalesOrderID kolone. Što radimo kad želimo upisati novi order, i header i details? Nešto ovakvo:
INSERT Sales.SalesOrderHeader (<columns>) VALES (<values>);
SET @SalesOrderID = SCOPE_IDENTITY();
INSERT Sales.SalesOrderDetail (SalesOrderID, <other_columns>) VALUES (@ID, <other_values>);
INSERT Sales.SalesOrderDetail (SalesOrderID, <other_columns>) VALUES (@ID, <other_values>);
Sve pet ako je samo jedan order u pitanju. A što ako želimo odjednom upisati nekoliko ordera? Npr:
INSERT Sales.SalesOrderHeader (<columns>) SELECT (<columns>) FROM OverseasSales.SalesOrderHeader WHERE <filter>;
INSERT Sales.SalesOrderDetail (SalesOrderID, <other_columns>) SELECT ???
Ako i uspijete saznati novogenerirane vrijednosti za SalesOrderID (hint: OUTPUT), kako ćete ih logički povezati s onim izvornima? OK, mi smo kreativni ljudi i smislit ćemo već nešto..
Nadalje, IDENTITY ne mora nužno biti unique (IDENTITY_INSERT, recimo). Bad.
Za identične podatke -- ako ih upisujemo u dva navrata -- možemo dobiti (ne samo da možemo, nego ćemo i dobiti) dvije različite vrijednosti u IDENTITY koloni. Isto je i ako upisujemo identične podatke u dvije različite baze. Pa onda poslije imamo problema s konsolidacijom (i trošimo novce na skupa MDM rješenja).
Vrijednost IDENTITY-ja se generira neovisno o transakciji. Npr:
SET NOCOUNT ON;
GO
USE tempdb;
GO
CREATE TABLE tbl (c1 int IDENTITY(1,1), c2 varchar(10));
GO
INSERT tbl VALUES ('qwerty');
BEGIN TRAN;
INSERT tbl VALUES ('asdfgh');
SELECT * FROM tbl;
ROLLBACK;
INSERT tbl VALUES ('asdfgh');
SELECT * FROM tbl;
DROP TABLE tbl;
c1 c2
----------- ----------
1 qwerty
2 asdfgh
c1 c2
----------- ----------
1 qwerty
3 asdfgh
Za drugi red ('asdfgh') smo prvo dobili vrijednost c1 kolone 2, a nakon ROLLBACK-a pri ponovnom upisu smo dobili 3. Plus toga, imamo i gap (hrvatski: rupu) u redoslijedu, što može i ne mora biti bitno.
A GUID.. Ajd, sad barem imamo NEWSEQUENTIALID(). I da, istina, morate imati uniqueidentifier kolonu da biste replicirali tablicu. Svejedno, ni to nije razlog da je proglasite za primary key :)
A što nam je htio reći onaj kolega s početka priče, kad nas je naputio da pročitamo članak o clustered indeksima s bloga Kimberly Tripp? To je jedan od onih "mitova i legendi" o kojima će, pretpostavljam, pričati Marko na bootcampu ovaj mjesec. Radi se naime o tome da su primary key i unique constrainti u SQL Serveru podržani preko (unique) indeksa. Što je sasvim OK. Ono što nije OK je to da će, ako eksplicitno ne kažete drugačije, SQL Server primary key podržati s clustered indeksom. A to nije OK iz najmanje dva razloga. Prvo, to stvara konfuziju kod ljudi, i prečesto se te dvije stvari poistovjećuju, iako se ustvari radi o dvije različite stvari -- jedno je integrity constraint, a drugo implementacija, tj kako je taj constraint realiziran -- pa se onda često priča o jednom, a misli se na drugo. Zatim, clustered indeks je jedna prilično važna alatka iz arsenala perf tuninga. Postavljanjem clustered indeksa na ovoj ili onoj koloni možemo nekad ubrzati određenu vrstu upita, recimo range query-je (ordered scan). Ne, ovime nisam želio reći da ćemo ako želimo ubrzati range upite obavezno postaviti CI na kolonu po kojoj je definiran range. Ovisi. Na koju kolonu ćemo postaviti clustered indeks, ili ga nećemo postaviti uopće, ovisi o podacima i njihovom korištenju, ali želimo li doista odreći se te mogućnosti?
Nakon svega ovoga -- ipak, IDENTITY uopće nije tako loš, ali važno je znati što nije i koja su ograničenja. Nekad je dapače i koristan -- može se postaviti unique constraint na njega i raditi DRI, pa čak i clustered indeks se može postaviti na njega. Ako imate razloga za to. Sve ovisi :)
Puno više o svemu ovome ćemo pričati na bootcampu u Varaždinu. Kog zanima, dobrodošao je. Early bird i dodatni popust za community još uvijek se mogu iskoristiti.
Pozdrav, čujemo se.
Dean