Duplicate check using SELECT or rely on unique index?

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

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.

Advertisement

Answer