Error Message:
Msg 10116, Level 16, State 1, Line 2
Cannot create %S_MSG on view ‘%.*ls’ because it contains one or more UNION, INTERSECT, or EXCEPT operators. Consider creating a separate indexed view for each query that is an input to the UNION, INTERSECT, or EXCEPT operators of the original view.
Severity level:
16.
Description:
This error message appears when you try to create an index on a view that contains a UNION, INTERSECT, or EXCEPT operator.
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. Consider creating a separate indexed view for each query that is an input to the UNION, INTERSECT, or EXCEPT operators of the original view.
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’) IS NOT NULL
DROP VIEW dbo.v;
GO
IF OBJECT_ID (‘dbo.t’) IS NOT NULL
DROP TABLE dbo.t;
GO
CREATE TABLE dbo.t
(
c1 int
);
GO
CREATE VIEW dbo.v
WITH SCHEMABINDING
AS
SELECT
1 AS c1
UNION ALL
SELECT
c1
FROM
dbo.t;
GO
CREATE UNIQUE CLUSTERED INDEX cix_v
ON dbo.v (c1);
GO
Remarks:
In the above example we try to index the view dbo.v. Because the view definition contains a UNION ALL operator, the error is raised.