How to apply the IGNORE_DUP_KEY index option to an existing index

• Steve Lin

  SSC Journeyman

  Points: 85

  More actions

  September 21, 2010 at 10:55 am

  #226734

  In SQL 2008 I can easily issue an 'ALTER TABLE dbo.tblName REBUILD WITH( IGNORE_DUP_KEY)' to add the IGNORE_DUP_KEY index option to an existing index.

  SQL 2000 does not have this syntax and from my research people recommend using the DBCC DBREINDEX command, yet this does not allow the specification of index options besides FILLFACTOR.

  My dbo.tblName has multiple FK references to other tables. Does anyone have a SQL 2000 script to be able to rebuild a table's indexes adding 'IGNORE_DUP_KEY = ON' option?

  I tried "Tasks..Generate scripts..." specifying target database of SQL 2000 yet the wizard still generated a SQL 2008 ALTER TABLE REBUILD WITH syntax script.

  Appreciate any help.

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  September 21, 2010 at 9:13 pm

  #1225318

  This option affects only new/recreated indexes.

  Try to use DBCC INDEXDEFRAG instead of rebuilding indexes.

  _____________
  Code for TallyGenerator