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.