SQL Server Performance

Unwanted Msg 3604 Duplicate key was ignored.

Discussion in 'Performance Tuning for DBAs' started by tdudley, Jun 11, 2004.

  1. tdudley New Member

    I wish to improve the performance of a stored procedure by using an 'insert into' a table with a 'unique index' along with the 'with ignore_dup_key' option instead of using a select distinct. Problem is I distributed a client side application that craps out when the stored procedure returns the informational message 3604. I can only think of one way to stop the message and that would be to remove that SQL Error message. I don't even know if that will work. Anybody got any server side workaround for the handling of this informational message?
  2. satya Moderator

    What is the service pack level on SQL Server?

    INSERT of batch job on a table that has a unique index with the IGNORE_DUP_KEY clause violates the uniqueness property of the index, the job step fails with the error 3604.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. tdudley New Member

    Satya,

    I'm using SQL Server 2000 Standard Edition 2000 Version 8.00.760 (SP3).

    It isn't really an error message perse it's an informational message. I'm using the functionality of not allowing duplicates to my advantage. It means I don't have to use select distinct, thus avoiding a hash match and in theory it should be much quicker. Problem is I don't want the informational message.
  4. satya Moderator

    True, its an informational message and actually it was corrected in SP1 of SQL 2K.
    So on that table try to eliminate duplicate rows or remove UNIQUE in index creation.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. Zirco New Member

    I believed what tdudley meant was that he does want to eliminate duplicates. And SQL2K can do that for free by using an index with IGNORE_DUP_KEY clause. He cannot drop the index, because this index is actually used for duplicate cleasing.

    The annoying side effects is that if there was at least one duplicate ignored by the index, SQL2K returns an error "Duplicate ignored". This is not really a runtime error because this is expected and sometimes intentional, like here.

    I was also looking to solve the same problem. I'm afraid there is no way to shut that error off. You might want to make an exception handling in your stored proc by testing @@ERROR. In my case, I just live with this annoying error. When there is an absolute requirement not to raise an error, I write specific SQL code to delete duplicates.
  6. satya Moderator

    Yes I understand and its introduced from SQL 2K onwards and to eliminate either follow the specified workaround or ignore the message.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page