none
What is function of the ignore_dup_key option?

    Question

  • Set ignore_dup_key to ON or OFF, what is the defference?

    I already check some documents and find they don't state it clearly.

    For instance: http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/36827;pt=36742/*

    Sunday, March 27, 2011 2:12 AM

Answers

  • > Set ignore_dup_key to ON or OFF, what is the defference?

    When IGNORE_DUP_KEY is OFF, a duplicate key value causes an error and the entire statement is rolled back. That is, if the statement attempted to insert multiple rows, no rows are inserted.

    When IGNORE_DUP_KEY is ON, a duplicate key value is simply ignored. The statement completes successfully and any other rows are inserted.

    This script demonstrates:

    CREATE TABLE ignore_dup_key (a int NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = ON))
    CREATE TABLE noignore_dup_key (a int NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = OFF))
    go
    INSERT ignore_dup_key(a) VALUES(1)
    INSERT ignore_dup_key(a) SELECT 1 UNION ALL SELECT 2
    go
    INSERT noignore_dup_key(a) VALUES(1)
    INSERT ignore_dup_key(a) SELECT 1 UNION ALL SELECT 2
    go
    SELECT a FROM ignore_dup_key
    SELECT a FROM noignore_dup_key
    go
    DROP TABLE ignore_dup_key
    DROP TABLE noignore_dup_key

    The output is:

    
    (1 row(s) affected)
    Duplicate key was ignored.
    
    (1 row(s) affected)
    
    (1 row(s) affected)
    Msg 2627, Level 14, State 1, Line 2
    Violation of PRIMARY KEY constraint 'PK__noignore__3BD0198E276EDEB3'.
    Cannot insert duplicate key in object 'dbo.noignore_dup_key'. The
    duplicate key value is (1).
    The statement has been terminated.
    a
    -----------
    1
    2
    
    (2 row(s) affected)
    
    a
    -----------
    1
    
    (1 row(s) affected)
    

    Note that the value 2 was instered in the ignore_dup_key table, but not in the other.

    IGNORE_DUP_KEY is a very old option, and you could question whether it is good practice to use it. I would be wary of putting that option on a permanent table, but it can be a good option if you need to cleanse a dataset from occasional duplicates. See this blogpost from SQL Server MVP Alex Kuznetsov: http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/07/30/performance-of-inserts-and-ignore-dup-key.aspx


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Marked as answer by jetq Sunday, March 27, 2011 1:12 PM
    Sunday, March 27, 2011 10:46 AM

All replies

  • ignore_dup_key is useful feature while you are having a unique index. In order to ensure the uniqueness of an index key and also guarantee your data insertion to be successful, you set the ignore_dup_key on.

    after you set the  ignore_dup_key on if your importing data have duplicated keys it will be ignored.

    Sunday, March 27, 2011 2:20 AM
  • Thank you for response. But I still don't understand clearly.

    If I set the ignore_dup_key to ON, can I insert a duplicated row into a table wjth unique index.

    Sunday, March 27, 2011 2:49 AM
  • ----------------Ignore Below-----------------------

    The answer is yes. for the duplicated rows only one row is inserted.

    Sunday, March 27, 2011 3:00 AM
  • HI jetq,

    I have to admit that my previous answer is not correct. the answer is NO!!

    If I set the ignore_dup_key to ON, your all duplicated rows will be discarded with no error.

    Sunday, March 27, 2011 7:39 AM
  • Hi,

    Ignore_duplicate _key is for use when creating a unique key on tables that already have data, in order to complete the process of building the constraint without failing due to historical bugs that insert duplicate rows, you can define the flag and the constraint will be build without checking the historical data.

    Please note that once the constraint has been created it will force uniqueness on future inserts.

    Thanks,


    אסף שלם
    Sunday, March 27, 2011 8:02 AM
  • > Set ignore_dup_key to ON or OFF, what is the defference?

    When IGNORE_DUP_KEY is OFF, a duplicate key value causes an error and the entire statement is rolled back. That is, if the statement attempted to insert multiple rows, no rows are inserted.

    When IGNORE_DUP_KEY is ON, a duplicate key value is simply ignored. The statement completes successfully and any other rows are inserted.

    This script demonstrates:

    CREATE TABLE ignore_dup_key (a int NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = ON))
    CREATE TABLE noignore_dup_key (a int NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = OFF))
    go
    INSERT ignore_dup_key(a) VALUES(1)
    INSERT ignore_dup_key(a) SELECT 1 UNION ALL SELECT 2
    go
    INSERT noignore_dup_key(a) VALUES(1)
    INSERT ignore_dup_key(a) SELECT 1 UNION ALL SELECT 2
    go
    SELECT a FROM ignore_dup_key
    SELECT a FROM noignore_dup_key
    go
    DROP TABLE ignore_dup_key
    DROP TABLE noignore_dup_key

    The output is:

    
    (1 row(s) affected)
    Duplicate key was ignored.
    
    (1 row(s) affected)
    
    (1 row(s) affected)
    Msg 2627, Level 14, State 1, Line 2
    Violation of PRIMARY KEY constraint 'PK__noignore__3BD0198E276EDEB3'.
    Cannot insert duplicate key in object 'dbo.noignore_dup_key'. The
    duplicate key value is (1).
    The statement has been terminated.
    a
    -----------
    1
    2
    
    (2 row(s) affected)
    
    a
    -----------
    1
    
    (1 row(s) affected)
    

    Note that the value 2 was instered in the ignore_dup_key table, but not in the other.

    IGNORE_DUP_KEY is a very old option, and you could question whether it is good practice to use it. I would be wary of putting that option on a permanent table, but it can be a good option if you need to cleanse a dataset from occasional duplicates. See this blogpost from SQL Server MVP Alex Kuznetsov: http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/07/30/performance-of-inserts-and-ignore-dup-key.aspx


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    • Marked as answer by jetq Sunday, March 27, 2011 1:12 PM
    Sunday, March 27, 2011 10:46 AM