How to fix Error 1990 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 1990 — Cannot Define Index on View with IGNOREDUPKEY

Why This Error Happens

This error occurs because SQL Server does not allow the IGNORE_DUP_KEY option to be used when creating a unique clustered index on an indexed view. The IGNORE_DUP_KEY setting is designed to silently skip duplicate rows during insert operations on standard tables, but indexed views require strict uniqueness guarantees to maintain the integrity of the materialized result set. When SQL Server detects this option during index creation on a view, it rejects the statement outright because allowing duplicates to be ignored would compromise the view’s indexed data consistency.

Step-by-Step Resolution Guide

Step 1: Identify the Offending CREATE INDEX Statement

  • Open SQL Server Management Studio (SSMS) and locate the script or stored procedure that failed with Error 1990.
  • Search for the CREATE UNIQUE CLUSTERED INDEX statement targeting the view in question.
  • Confirm the presence of the WITH (IGNORE_DUP_KEY = ON) clause in the syntax.

Step 2: Remove the IGNOREDUPKEY Option

  • Edit the CREATE INDEX statement to strip out IGNORE_DUP_KEY = ON entirely.
  • Example corrected syntax:
  CREATE UNIQUE CLUSTERED INDEX IX_MyView_Unique
  ON dbo.MyIndexedView (ColumnA, ColumnB);
  • Save the modified script to a working directory such as C:\SQLScripts\IndexFixes\.

Step 3: Validate the View Definition for Duplicate Rows

  • Run a SELECT query grouping by the indexed columns to check for duplicates before re-creating the index:
  SELECT ColumnA, ColumnB, COUNT(*)
  FROM dbo.MyIndexedView
  GROUP BY ColumnA, ColumnB
  HAVING COUNT(*) > 1;
  • If duplicates are returned, revise the underlying view’s SELECT statement (e.g., add proper JOIN conditions or filters) so that the result set guarantees uniqueness.

Step 4: Re-create the Index Without IGNOREDUPKEY

  • Execute the corrected CREATE UNIQUE CLUSTERED INDEX statement against the view.
  • Confirm the index builds successfully in SSMS under Databases > [YourDB] > Views > [YourView] > Indexes.

Step 5: Restart SQL Server Service (If Index Creation Still Fails)

  • Open Services.msc on Windows Server 2025 / Windows 11.
  • Locate SQL Server (MSSQLSERVER) or the named instance, right-click, and select Restart.
  • Re-run the index creation script after the service comes back online.

Alternative or Verification Steps

  • Verify the fix by querying sys.indexes and sys.views to confirm the new index exists without the ignore_dup_key flag set (check the ignore_dup_key column value, which should be 0).
  • If the view’s logic cannot easily be changed to prevent duplicates, consider dropping the unique index requirement and instead use a non-unique clustered index, understanding that this changes the performance and integrity guarantees of the indexed view.