Duplicate check using SELECT or rely on unique index?

Question

Using SQL Server 2012. I want to insert unique strings into a table. I always want to return the row ID of the unique string. Now, this can be accomplished in two ways.

Which solution is the best?

This is the table in question:

CREATE TABLE [dbo].[Comment](
    [CommentID] [int] IDENTITY(1,1) NOT NULL,
    [Comment] [nvarchar](256) NOT NULL

    CONSTRAINT [PK_Comment] PRIMARY KEY CLUSTERED([CommentID] ASC)
)

CREATE UNIQUE NONCLUSTERED INDEX [IX_Comment_Comment] ON [dbo].[Comment]
(
    [Comment] ASC
)

Solution 1:

SELECT first to check if the string exists. If it does, return its ID. Otherwise, INSERT a new row and return the newly created ID.

CREATE PROCEDURE [dbo].[add_comment]
    @Comment [nvarchar](256)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @CommentID [int]
    DECLARE @TransactionCount [int]

    BEGIN TRY
        SET @TransactionCount = @@TRANCOUNT

        IF @TransactionCount = 0
            BEGIN TRANSACTION

        SELECT @CommentID = [CommentID] FROM [dbo].[Comment] WHERE [Comment] = @Comment

        IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO [dbo].[Comment]([Comment]) VALUES (@Comment)

            SET @CommentID = SCOPE_IDENTITY()
        END

        IF @TransactionCount = 0
            COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 AND @TransactionCount = 0 
            ROLLBACK TRANSACTION

        ; THROW
    END CATCH

    RETURN @CommentID
END

Solution 2:

INSERT first. If the insert violates the UNIQUE INDEX, a SELECT is issued.

CREATE PROCEDURE [dbo].[add_comment2]
    @Comment [nvarchar](256)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @CommentID [int]

    BEGIN TRY
        INSERT INTO [dbo].[Comment]([Comment]) VALUES (@Comment)
        SET @CommentID = SCOPE_IDENTITY()
    END TRY
    BEGIN CATCH
        IF @@ERROR = 2601 -- Duplicate
            SELECT @CommentID = [CommentID] FROM [dbo].[Comment] WHERE [Comment] = @Comment
        ELSE
            THROW
    END CATCH

    RETURN @CommentID
END
GO

Solution 3:

Ideas? :)

Answer 1

In my testing I have found that it is much more efficient to check for the violation first instead of letting SQL Server try and fail, especially when the failure rate is expected to be significant (and at best they perform about the same overall, when the failure rate is low). Details here and here.

In addition to performance, another reason to not rely on the constraint to raise an error is that tomorrow someone could change or drop it.

Answer 2

Personally I do it like this

INSERT INTO [dbo].[Comment]([Comment])
SELECT T.Comment
FROM (SELECT @Comment) T (Comment)
LEFT JOIN dbo.Comment C ON C.Comment = T.Comment
WHERE C.Comment IS NULL

SELECT @CommentID = CommentID
FROM dbo.Comment C
WHERE C.Comment = @Comment

Answer 3

You should also encapsulate it all within a transaction. I like the idea of a conditional insert.

Answer 4

I think this is the simplest option:

SET NOCOUNT ON;

BEGIN TRAN
    /* Insert if doesn't already exist */
    INSERT INTO dbo.comment (comment)
    SELECT @comment
    WHERE  NOT EXISTS (
             SELECT comment
             FROM   dbo.comment
             WHERE  comment = @comment
           );

    /* Return comment id */
    SELECT commentid
    FROM   dbo.comment
    WHERE  comment = @comment;
END TRAN

Alternative to the insert part if you prefer the newer MERGE syntax (please read this article by Aaron Bertrand first):

...
; WITH source AS (
  SELECT @comment As comment
)
MERGE INTO dbo.comment As destination
  USING source
    ON source.comment = destination.comment 
WHEN NOT MATCHED THEN
  INSERT (comment)
    VALUES (source.comment)
;
...

Answer 5

I would go with insert first then grab the result. The db will do the check internally. You will need to write the logic for duplicate key anyway so use it. This way you make 1 call to the database not 2. Also, who knows what would happen between the 2 calls?

Edit: Based on the run results from Aaron Bertrand - This answer is not always valid.