CODEWITHSUNDEEP
sql-serversql-server-2005suppress-warnings
Tadmas
Tadmas

Reputation: 6378

Suppress T-SQL warnings generated by IGNORE_DUP_KEY

Is there a way to suppress warnings generated by inserting duplicate keys on indexes with IGNORE_DUP_KEY = ON? The warning it gives is "Duplicate key was ignored."

I am investigating using this option with a table variable for performance reasons, but it generates a lot of warning messages. This can be a problem since I log the output of my script to a text file, and I don't want the log file to fill up with hundreds of lines of this warning over and over.

The warnings can be reproduced with the following script. Note: this is not my actual script, but merely an example for illustration purposes.

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;

DECLARE @data TABLE (x int NOT NULL PRIMARY KEY);
DECLARE @i int;

SET @i = 1;
WHILE @i <= 1000
BEGIN
    INSERT INTO @data (x) VALUES (@i);
    SET @i = @i + 1;
END;

DECLARE @test TABLE (x int NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = ON));
SET @i = 2;
WHILE @i <= 1000
BEGIN
    INSERT INTO @test (x) SELECT x FROM @data WHERE (x % @i) = 0 AND x > @i
    SET @i = (SELECT MIN(x) FROM @data d WHERE x > @i
        AND NOT EXISTS (SELECT 1 FROM @test t WHERE t.x=d.x));
END;

SELECT COUNT(*) FROM @test;

This is similar to How do I suppress T-SQL warnings when running a script SQL Server 2005?, but the solution there does not work in this case. I have also seen suggestions for using TRY...CATCH, but that does not work either: it appears you can't catch warnings.

I am using SQL Server 2005.

Upvotes: 3

Views: 4191

Answers (2)

Paul White
Paul White

Reputation: 374

In SQL Server 2017 and later, there is an option for this, though it is presently undocumented:

DECLARE @test table 
(
    x int NOT NULL 
        PRIMARY KEY CLUSTERED
        WITH 
        (
            IGNORE_DUP_KEY = ON
                (SUPPRESS_MESSAGES = ON)
        )
);

For more background on the performance and execution plan implications, see my article Why IGNORE_DUP_KEY is slower on clustered indexes.

Upvotes: 5

user121489
user121489

Reputation:

What if you removed the "WITH (IGNORE_DUP_KEY = ON)" option from the table declaration and instead wrapped your INSERT statement using a try/catch block. See the example below:

BEGIN TRY 
  INSERT INTO @test (x) SELECT x FROM @data WHERE (x % @i) = 0 AND x > @i 
END TRY
BEGIN CATCH
  IF NOT (ERROR_NUMBER() = 2627 AND ERROR_SEVERITY() = 14)
    PRINT 'Real error occurred.'
END CATCH

Upvotes: -1

Related Questions