who knows

[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

[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.

[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

[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.