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

by

Troubleshooting Guide: Error 1940 — “Cannot create %S_MSG on view ‘%.*ls’. It does not have a unique clustered index.”

Why This Error Happens

This error occurs when you attempt to create an indexed (materialized) view or add a unique/primary constraint on a view in SQL Server 2025 without first establishing a unique clustered index on that view. SQL Server requires the first index created on a view to be a unique clustered index because this physically materializes the view’s result set on disk, and subsequent nonclustered indexes depend on that structure existing first. This commonly happens when the CREATE INDEX statement omits the UNIQUE keyword, when the view’s SELECT statement lacks deterministic columns, or when the underlying columns referenced can’t guarantee row uniqueness.

Step-by-Step Resolution Guide

Step 1: Verify the View Definition Meets Indexing Requirements

  • Confirm the view is created WITH SCHEMABINDING, as this is mandatory for any indexed view.
  • Ensure the SELECT statement does not use SELECT *, outer joins, UNION, subqueries, or non-deterministic functions (e.g., GETDATE()).
  • Run the following to inspect the view’s current definition:
  EXEC sp_helptext 'YourSchema.YourViewName';

Step 2: Create the Correct Unique Clustered Index First

  • Identify a column or combination of columns in the view that guarantees row-level uniqueness (e.g., a primary key column from the base table).
  • Execute the clustered index creation before attempting any other index:
  CREATE UNIQUE CLUSTERED INDEX IX_ViewName_Unique
  ON YourSchema.YourViewName (UniqueColumn1, UniqueColumn2);
  • Do not attempt CREATE INDEX (nonclustered) on the view until this step succeeds.

Step 3: Adjust Session-Level SET Options

  • Confirm the following SET options are correctly configured in your session or connection string, as indexed views require strict settings:
  • ANSI_NULLS ON
  • ANSI_PADDING ON
  • ANSI_WARNINGS ON
  • ARITHABORT ON
  • CONCAT_NULL_YIELDS_NULL ON
  • QUOTED_IDENTIFIER ON
  • NUMERIC_ROUNDABORT OFF
  • Set these explicitly at the top of your script before creating the index:
  SET ANSI_NULLS ON;
  SET QUOTED_IDENTIFIER ON;
  SET ARITHABORT ON;

Step 4: Rebuild Dependent Objects

  • If the view was altered to fix schemabinding or column issues, check for and rebuild any dependent indexes, statistics, or foreign views that failed due to the broken dependency chain.
  • Use sys.sql_expression_dependencies to map out objects relying on this view before re-running index creation scripts.

Alternative or Verification Steps

  • Verify the fix by querying sys.indexes and confirming index_id = 1 (clustered) exists with is_unique = 1 for the view’s object ID:
  SELECT i.name, i.index_id, i.is_unique
  FROM sys.indexes i
  WHERE i.object_id = OBJECT_ID('YourSchema.YourViewName');
  • If the unique clustered index still fails to create, check for duplicate data in the underlying base tables using a GROUP BY ... HAVING COUNT(*) > 1 query on your intended key columns — duplicates will block uniqueness enforcement even if the syntax is correct.