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

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.

CodePudding user response：

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.

CodePudding user response：

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.