Fastest way to insert a new record if not exists, double check?

Question

Here is a simple table definition:

CREATE TABLE UserNotificationTokens (
    UserID INT NOT NULL,
    Token CHAR(36) NOT NULL,
    DeviceId VARCHAR(50) NOT NULL,
    CONSTRAINT UC_DeviceId UNIQUE (DeviceId)
)

Here, DeviceId column represents a physical device (smartphones). For each device, a user has a token (just a string, no big deal). As you can see, there is a unique key on DeviceId column. I want to insert a new token for a device if this device (DeviceId) does not exist already. I can check this existence in a few ways. But, the point is SQL Server already checks if DeviceId is unique because of the Unique Key. That means, there is double work for the same task. Is it a good practice to just ignore my own checking and leave it to SQL Server? If the insert will cause an error with code 2601 (Violation of Unique Key constraint), that means DeviceId is already there. Catch this specific error and do nothing. Otherwise, the row was inserted successfully.

If you're only inserting 1 row at a time, why bother to check first, when SQL Server will handle it automatically and return an error you can easily handle? I can only see usefulness if you're inserting many rows at a time, and want to omit the one's that are duplicates and/or do something else with them. — Thom A, Dec 09 '21 at 09:01
Does this answer your question? [How to upsert (update or insert) in SQL Server 2005](https://stackoverflow.com/questions/11010511/how-to-upsert-update-or-insert-in-sql-server-2005) — Charlieface, Dec 09 '21 at 09:30
See also https://sqlperformance.com/2020/09/locking/upsert-anti-pattern — Charlieface, Dec 09 '21 at 09:30
Since this is a conditional `insert` without the `update` alternative, just use a `where not exists()` — George Menoutis, Dec 09 '21 at 11:32

score 1 · Answer 1 · answered Dec 09 '21 at 09:04

1

Well, there are 3 different ways to do it. ALl with their own advantages.

Check, then insert. In a transaction. This takes time (2 commands, 2 round trips to the database) thus removing throughput, especially as you need a table lock to make sure it is 100% accurate.
Use a merge statement to write the change in one go. Yes, doable.
Just insert and catch the exception.

I would go with the later. It is fastest - one round trip only, no table lock, no latency to the server) and catching the exception is fast.

How you do it really depends on the complexity - sometimes offloading simple things like that to the db just makes most sense.

answered Dec 09 '21 at 09:04

TomTom

61,059
10
88
148

1

don't use MERGE; it's very broken. – Mitch Wheat Dec 09 '21 at 09:05
Nope. It is not exactly trivial, but I am using it to great advantage. See, it is all about knowing limitations and reading the documentation. – TomTom Dec 09 '21 at 09:06
the documentation doesn't tell you about the hundreds of bugs that have so far been found in MERGE. I wonder if you think it is atomic?? – Mitch Wheat Dec 09 '21 at 09:08
Another way: ignore duplicates on index setting and then check for affected rows – Ramin Rahimzada Dec 09 '21 at 09:17
Another way: Do the `UPDATE` then check `@@ROWCOUNT` as to whether to do the `INSERT` – Charlieface Dec 09 '21 at 09:31
@TomTom: here's some documentation for you: https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ – Mitch Wheat Dec 09 '21 at 10:38

score 0 · Answer 2 · answered Dec 09 '21 at 15:20

0

You could use Insert Ignore:

Example:

INSERT IGNORE INTO books

    (id, title, author, year_published)

VALUES

    (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960);

using this query values will be inserted only if it is unique.

answered Dec 09 '21 at 15:20

lakshna S

255
1
5

Fastest way to insert a new record if not exists, double check?

2 Answers2