question

sathishkumar avatar image
sathishkumar asked

Create Index

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)
sql-server-2000index
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
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
11 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

haven't got a 2000 instance available to me to test but can you try `create unique index ind_dup_1 on dup_index(firstname) with IGNORE_DUP_KEY`
2 Likes 2 ·
I have created new table and tried to create index for that table..there is no values..
0 Likes 0 ·
ahh I was guessing what the error was!
0 Likes 0 ·
Haven't got a 2000 instance to test on, but try removing the brackets `create unique index ind_dup_1 on dup_index(firstname) with IGNORE_DUP_KEY = ON`
0 Likes 0 ·
i would have tried removing brackets but it showing the same error.
0 Likes 0 ·
Show more comments
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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..
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
The 2000 syntax is different - it is create unique index ind_dup_1 on dup_index(firstname) with IGNORE_DUP_KEY No brackets, no =, no ON
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.