In my code I generate a list of IDs then input associated data into a table with ID as the Primary Key. Is it better practice to let SQLSever throw an error and handle it (Integrity Error)? Or should I preemptively check if that ID exists in the server and then perform the insert?
Advertisement
Answer
If you are inserting multiple rows and one of them violates the constraint, then in general, none of the rows will be inserted.
This can be a pain. For that reason, you might want to check before you insert multiple rows at the same time. This helps guarantee that the data is valid; of course, race conditions can still generate problems when the “real” query is run.
That said, doing the check as a separate query increases overhead (an additional round trip). The database is going to do the check anyway (so it is redundant). And, you cannot remove the check from the database (because of race conditions).
Some people also like to check that things are okay first, so auto-incremented/identity ids are less likely to have gaps. Of course, gaplessness is not guaranteed, so this just reduces one cause of gaps.
This db<>fiddle illustrates these issues.