Error Message:
Msg 10136, Level 16, State 1, Line 2
Cannot create %S_MSG on view “%.*ls” because it contains one or more subqueries. Consider changing the view to use only joins instead of subqueries. Alternatively, consider not indexing this view.
Severity level:
16.
Description:
This error message appears when you try to index a view that contains subqueries.
Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.
Resolution:
Errors of the Severity Level 15 are generated by the user and can be fixed by the SQL Server user. The statement cannot be executed this way. You cannot index a view that contains subqueries.
Versions:
This error message was introduced with SQL Server 2000.
Example(s):
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING,
ANSI_WARNINGS,
CONCAT_NULL_YIELDS_NULL,
ARITHABORT,
QUOTED_IDENTIFIER,
ANSI_NULLS ON;
GO
IF OBJECT_ID (‘dbo.V’, ‘View’) > 0
DROP VIEW dbo.V;
GO
IF OBJECT_ID (‘dbo.t’) IS NOT NULL
DROP TABLE dbo.t;
GO
CREATE TABLE dbo.t
(
c1 int NOT NULL PRIMARY KEY
);
GO
CREATE VIEW dbo.V
WITH SCHEMABINDING
AS
SELECT
c1, COUNT_BIG(*) AS myCount, (SELECT 1) AS foo
FROM
dbo.t
GROUP BY
c1;
GO
CREATE UNIQUE CLUSTERED INDEX cix_t_v
ON dbo.V (c1);
GO
Remarks:
In the above example we try to create an index on view dbo.V. Because the view definition contains a subquery, the error is raised.