Hi, Im trying to create an index using IGNORE_DUP_KEY = ON in sql server 2000, when executing the query it showing syntax error. help me to correct the error.. query create unique index ind_dup_1 on dup_index(firstname) with (IGNORE_DUP_KEY = ON)
If you already have data in the table, with duplicate values, then the unique index cannot be created. To add this index to a table where duplicate values already exists, you will have to de-dupe the data first. The `IGNORE_DUP_KEY` option only controls the response when you subsequently try to insert duplicate values into the table. - ON : means that a warning message will be given and only the rows violating the unique constraint will fail to b inserted - OFF (the default) : means that the whole insert statement will fail and will be rolled back ---- Edit : follow the comments on this answer and we eventually get to the answer: create unique index ind_dup_1 on dup_index(firstname) with IGNORE_DUP_KEY
On SQL 2000 it should be without the `=` create unique index ind_dup_1 on dup_index(firstname) with IGNORE_DUP_KEY ON Created a test table on SQL 2008, and generated script including the index with the script compatibility set to SQL 2000..