Optimal way to insert a unique record into table
Posted on 2007-11-26
What is the correct way to insert a value into a table where that value must be unique? There seems to be two ways of doing this:
Assume myValue has a unique constraint index, and ofcourse this is not real T-SQL just pseudocode but you'll get the drift, also assume that the database is extremely busy with one of the following two pieces of code being executed continuously by many users and processes.
select count (*) from mytable where myValue = '1234567890'
if count = 0 then
insert mytable(myalue) values('1234567890')
insert mytable(myvalue) values ('1234567890')
if index-collision-error then
My concern with option 1 is that it might be possible for a competing process/user to insert the value '1234567890' into myTable after the select statement is executed but before the insert statement is executed. Is this possible or does SQL Server prevent this from occuring?
As for option 2 - well it just seems inefficient to throw exceptions all over the place.
Perhaps there is another (correct) way to do this.