Member Avatar for noamwegner

what are the diffrences between CREATE INDEX [WITH IGNORE_DUPLICATE_KEY OFF]
and CREATE UNIQUE INDEX
it sounds the same!
and if anyone knows when creating a plain INDEX just CREATE INDEX
whether it creates a clustered or nonclustered index
couldnt find an anshwer to that
thanks in advance

  • 2 Contributors
  • 3 Replies
  • 121 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by darkagn

Dani AI

Generated

Quick summary and a few practical clarifications that build on 's answers.

CREATE INDEX versus CREATE UNIQUE INDEX

  • CREATE UNIQUE INDEX enforces uniqueness on the index key columns; CREATE INDEX (without UNIQUE) does not enforce uniqueness. The uniqueness test is performed across all key columns together (row1.A = row2.A AND row1.B = row2.B ...).
  • The index option that controls what happens when a duplicate is attempted is IGNORE_DUP_KEY. It only applies to unique indexes. With IGNORE_DUP_KEY = OFF (the default) a duplicate insert/update causes an error and the statement fails; with IGNORE_DUP_KEY = ON SQL Server skips the duplicate row(s) and returns a warning so the rest of the statement can continue.

Example syntax

CREATE UNIQUE INDEX IX_MyIndex
  ON dbo.MyTable (A, B, C)
  WITH (IGNORE_DUP_KEY = ON);

Practical notes and gotchas

  • Included columns (the INCLUDE(...) clause) do not participate in uniqueness checks — only the index key columns do.
  • IGNORE_DUP_KEY = ON can make bulk loads less painful but can mask data problems; prefer deduplicating in staging or using logic that explicitly filters duplicates when correctness matters.
  • CREATE INDEX defaults to NONCLUSTERED if you do not specify CLUSTERED or NONCLUSTERED. A clustered index determines the table order and you can have only one per table; primary key constraints by default create a clustered index unless you specify otherwise.

Microsoft documentation for syntax and options:
CREATE INDEX (Transact-SQL)

Recommended Answers

All 3 Replies

Member Avatar for darkagn

A non-unique index allows duplicates to be inserted where a duplicate is defined as having the exact same values across each of the columns in the index. A unique index will raise an error and rollback an insert or update transaction when this check is failed.

If the CREATE INDEX query does not include the CLUSTERED or NONCLUSTERED key words, then the index will be a NONCLUSTERED index by default.

Edited by darkagn because: n/a
Member Avatar for noamwegner

A non-unique index allows duplicates to be inserted where a duplicate is defined as having the exact same values across each of the columns in the index.

so if the colmuns a , b and c all have "blah" as value in one row and the index is on them
then if IGNORE_DUPLICATE_KEY is OFF
will raise an error?
but if column a will have all his rows with the value "bla" (in a non unique index)
it will be OK?
and thanks a lot for answering

Member Avatar for darkagn

Ah sorry I misread your OP and missed the part about IGNORE_DUPLICATE_KEY. Note that this option cannot be set to ON for a non-unique index, so it really only applies to unique indexes. When this option is ON, only a warning will be generated but the insert or update containing a duplicate will be allowed. When OFF (which is the default), the error is raised and the transaction is rolled back as I described earlier.

For an index on columns A, B and C that is unique, rows are considered duplicates if row1.A = row2.A AND row1.B = row2.B AND row1.C = row2.C. Note that this doesn't mean that column A = column B = column C, just that the values in the two rows match.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.