USEFUL SITES :
Write for Us
Error Message:Msg 10137, Level 16, State 1, Line 2Cannot 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 are corrigible by the 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;GOSET NUMERIC_ROUNDABORT OFF;SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON;GOIF OBJECT_ID ('dbo.MyOrderView', 'View') > 0 DROP VIEW dbo.MyOrderView;GOIF OBJECT_ID('tempdb.dbo.t') > 0 DROP TABLE tGO CREATE TABLE t( id INT, reportToID INT NULL,)INSERT INTO t SELECT 1, NULLUNION ALL SELECT 2, 1UNION ALL SELECT 3, 1UNION ALL SELECT 4, 2GOCREATE VIEW dbo.MyOrderViewWITH SCHEMABINDINGASWITH 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 MyCTEGO
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.