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
  • 102 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by darkagn
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.