I have the following table set up:
Id int pk, unique not null Name varchar(50) not null- Other columns not relevant to this issue
With an index set up on Name to be unique and non-clustered.
The setup does EXACTLY what I want - that is, only insert new rows whose Name doesn't already exist in the table, and throw an error if the new row is a duplicate Name.
I might be nit-picky about it, but every attempt to add a duplicate will cause SQL Server to skip the next Id that would have been assigned, had the new row been a non-duplicate Name.
Is there a way to prevent this with some setting, without the need to query for existence first before deciding to insert or deny?