Nyligen stötte jag på en fråga som lyder:

Hur skriver jag en stored procedure för att göra en insert bara om det inte redan finns en rad med de värden jag försöker göra insert med.

Mitt svar är:

Använd IF NOT EXISTS(SELECT * FROM tabellen where kolumn1 = @kolumn1 AND kolumn2 = @kolumn2 [..] AND kolumnN = @kolumnN

Den som ställde frågan stötte på syntaktiska problem, och återkom med beskedet:

Jag har löst det. Jag skapade ett icke-klustrat unikt index med alternativet WITH IGNORE_DUP_KEY

Såhär alltså:

CREATE UNIQUE NONCLUSTERED INDEX UQI_tabellen_all ON tabellen(kolumn1, kolumn2, [..], kolumnN) WITH IGNORE_DUP_KEY

Fiffigt. Då behöver man inte programmera sin kontroll. Man kan bara göra en insert, och om en identisk rad redan finns så händer ingenting. Eller nästan ingenting iallafall. Sanningen är att SQL Server kastar ifrån sig en varning: "Duplicate key was ignored". Men det är ingenting jag i de flesta fall märker i min ADO.NET-kod. Där tror jag att allt gått som det ska, och kör vidare till nästa rad.

Frågan är om det verkligen är vad jag vill. Eller rättare sagt. Det här är INTE vad jag vill. Jag vill verkligen inte att SQL Server ska låtsas som att den har gjort en INSERT när den inte har gjort det. Försöker jag göra insert i en tabell där det finns en unique-constraint så vill jag veta att jag bryter mot databas-regler. Jag vill ha ett stort fett ERROR kastat när jag försöker bryta mot en unique-constraint.

Ett STORT problem med lösningen är också att den som frågade hade gjort ett ICKEKLUSTRAT index istället för ett KLUSTRAT. Det innebär att allt data dubbellagras, dels i tabellen själv, dels i indexträd för tabellen. Ska man använda sig av heltäckande index så ska de vara klustrade!

Jag vill alltså göra kontrollen med EXISTS-funktionen som jag beskriver ovan. Till min STORA glädje upptäcker jag också att det är mycket effektivare att göra så. Exakt varför vet jag inte, men troligen är det så att det snor en del resurser från SQL Server att konstatera att ett brott mot en constraint håller på att begås, och därför ge ifrån sig en varning och ignorera den fysiska inserten, istället för att "bara" göra en EXISTS-kontroll (som ju SQL Server ändå måste göra för att upprätthålla UNIQUE-constrainten).

Jag testade genom att först skapa en tabell:

create table t (id int identity(1,1) primary key nonclustered, c1 varchar(10), c2 varchar(10), c3 varchar(10))

Sedan skapade jag ett klustrat unique-index med WITH IGNORE_DUP_KEY:

create unique clustered index uqi_t_all ON t(c1,c2,c3) WITH IGNORE_DUP_KEY
go

Slutligen ett script som loopar 100000 gånger för att göra en INSERT:

declare @c1 varchar(10), @c2 varchar(10), @c3 varchar(10)
set @c1='1'
set @c2='2'
set @c3='3'
declare @i int
set @i=0
while @i<100000
begin
insert into t(c1,c2,c3) values(@c1,@c2,@c3)
set @i=@i+1
end

Scriptet körs på 12 sekunder

Jag testar igen, denna gång med ett unique index UTAN WITH IGNORE_DUP_KEY

drop index t.uqi_t_all
go
create unique clustered index uqi_t_all ON t(c1,c2,c3)
go

Jag kör sedan mitt insert-script igen, den här gången med en EXISTS-kontroll:

declare @c1 varchar(10), @c2 varchar(10), @c3 varchar(10)
set @c1='1'
set @c2='2'
set @c3='3'
declare @i int
set @i=0
while @i<100000
begin
IF NOT EXISTS(SELECT * FROM t where c1=@c1 AND c2=@c2 AND c3=@c3)
insert into t(c1,c2,c3) values(@c1,@c2,@c3)
set @i=@i+1
end

Scriptet körs på 1-2 sekunder

6-12 gånger snabbare alltså att göra kontroll i T-SQL istället för att låta SQL Server ignorera en insert genom IGNORE_DUP_KEY. Dessutom betydligt tydligare - alla kan se i SQL-koden att ingen insert kommer att göras om det skulle innebära skapande av dubletter. Med IGNORE_DUP_KEY är det betydligt mer otydligt.

Jag är nästan lite småsur över att IGNORE_DUP_KEY överhuvudtaget finns som ett alternativ för unique index. Varför gör man så? Det skapar risk för missförstånd och förvirring, och det är långsammare än att göra kontrollen i Transact-SQL.

För mer information om klustrade och icke-klustrade index: Vaddå klusterindex?