IGNORE_DUP_KEY option in SQL Server

I did quite some search in MSDN and Google, but looks like the description for IGNORE_DUP_KEY option is very limited.

My confusions,

  • How can I make a primary key as AUTOINCREMENT
  • How to iterate a string and add string to every position?
  • The entity framework and database agnostic programming, possibilities?
  • Formatting XML into a select statement
  • The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time, cannot free up more resiources
  • How to perform Lookups in Azure Data Factory?
    1. Is IGNORE_DUP_KEY option an option for a column? for a table? for a couple of columns? for an index (making index unique)?

    2. If set IGNORE_DUP_KEY to Yes, when insert a batch of records (using bulk insert WriteToServer ADO.Net function) with duplicate keys (for example, I insert some values which already exist in database), SQL Server will not throw an error. The batch job will be completed successfully but the duplicated rows will not be inserted. All other rows will be inserted and SQL Server treat it as a job success. Is my understanding correct?

    thanks in advance,
    George

  • Handling multiple records in a MS SQL trigger
  • Insert a csv file into Azure SQL from an Azure Blob
  • SQL Server Cyrillic Writing '?????'
  • Use send_dbmail to send an email for each row in sql table
  • how to connect sql server using window auth using clojure?
  • Is adding a bit mask to all tables in a database useful?
  • 2 Solutions collect form web for “IGNORE_DUP_KEY option in SQL Server”

    IGNORE_DUP_KEY is an option of CREATE INDEX and only affects inserts of multiple rows:

    IGNORE_DUP_KEY = ON

    • all unique rows get inserted, a warning is issued, and the duplicate rows are not inserted

    IGNORE_DUP_KEY = OFF

    • an error is issued and no rows are inserted
    1. IGNORE_DUP_KEY is for a given UNIQUE INDEX.

    2. “After the cancellation, any transaction that was active at the time may continue as though the update or insert had never taken place. Nonduplicate keys are inserted normally.” So yes, bulk inserts will report success.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.