Cannot create %S_MSG on view "%.*ls" because it references common table expression "%.*ls". Views referencing common table expressions cannot be indexed. Consider not indexing the view, or removing the common table expression from the view definition

Error Message:
Msg 10137, Level 16, State 1, Line 2
Cannot create %S_MSG on view “%.*ls” because it references common table expression “%.*ls”. Views referencing common table expressions cannot be indexed. Consider not indexing the view, or removing the common table expression from the view definition.

Severity level:
16.

Description:
This error message appears when you try to reference a common table expression in an indexed view.

Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.

Resolution:
Errors of the Severity Level 16 are generated by the user and can be fixed by the SQL Server user. The statement cannot be executed this way. You cannot reference a common table expression in an indexed view.

Versions:
This error message was introduced with SQL Server 2005.

Example(s):
USE tempdb;
GO
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING,
    ANSI_WARNINGS,
    CONCAT_NULL_YIELDS_NULL,
    ARITHABORT,
    QUOTED_IDENTIFIER,
    ANSI_NULLS ON;
GO
IF OBJECT_ID (‘dbo.MyOrderView’, ‘View’) > 0
   DROP VIEW dbo.MyOrderView;
GO
IF OBJECT_ID(‘tempdb.dbo.t’) > 0
 DROP TABLE t
GO
CREATE TABLE t
(
 id INT,
 reportToID INT NULL,
)
INSERT INTO t SELECT 1, NULL
UNION ALL SELECT 2, 1
UNION ALL SELECT 3, 1
UNION ALL SELECT 4, 2
GO
CREATE VIEW dbo.MyOrderView
WITH SCHEMABINDING
AS
WITH MyCTE (id, reportToID, Level)
AS
(
 SELECT t1.ID, t1.reportToID, 0 AS Level
   FROM dbo.t AS t1
  WHERE reportToID IS NULL
  UNION ALL
 SELECT t1.ID, t1.reportToID, Level + 1
   FROM dbo.t AS t1
   JOIN MyCTE AS t2
     ON t1.reportToID = t2.ID
)
SELECT id, reportToId, Level
  FROM MyCTE
GO

CREATE UNIQUE CLUSTERED INDEX cix_MyOrderView
    ON dbo.MyOrderView (ID);
GO

Remarks:
In the above example we try to reference the common table expression MyCTE in an indexed view. This raises the error.

]]>

Leave a comment

Your email address will not be published.