I am investigating the effect of various SET options on the creation of indexed views on SQL Server 2014 CU1. The following code:
- ensures that the SET options are as they should be
- creates a table
- creates a view based on the table
- creates an index on the view
- inserts a record in the view (which will fail if the SET options are incorrect)
It works as expected. However, if I uncomment the section where I deliberately set each SET option to the wrong value, the CREATE INDEX statement (which occurs earlier in the script) fails saying that QUOTED_IDENTIFIER has the wrong value.
I have tested with SSMS 2012 and 2014. How can this be?
USE tempdb; -- ensure correct SET options for indexed view creation -- http://msdn.microsoft.com/en-us/library/ms191432.aspx SET ANSI_NULLS ON; SET ANSI_PADDING ON; SET ANSI_WARNINGS ON; SET ARITHABORT ON; SET CONCAT_NULL_YIELDS_NULL ON; SET NUMERIC_ROUNDABORT OFF; SET QUOTED_IDENTIFIER ON; IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'v1' AND TABLE_SCHEMA = 'dbo') DROP VIEW dbo.v1; IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'dbo') DROP TABLE t1; CREATE TABLE dbo.t1(c1 int); GO CREATE VIEW dbo.v1 WITH SCHEMABINDING AS SELECT c1 FROM dbo.t1; GO CREATE UNIQUE CLUSTERED INDEX i1 ON dbo.v1(c1); ---- make SET options the opposite of what is required --SET ANSI_NULLS OFF; --SET ANSI_PADDING OFF; --SET ANSI_WARNINGS OFF; --SET ARITHABORT OFF; --SET CONCAT_NULL_YIELDS_NULL OFF; --SET NUMERIC_ROUNDABORT ON; --SET QUOTED_IDENTIFIER OFF; INSERT INTO dbo.v1(c1) VALUES(1); DROP VIEW dbo.v1; DROP TABLE dbo.t1;
I can work around it with dynamic SQL and get the expected error message:
Msg 1934, Level 16, State 1, Line 32 INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'...
-- make SET options the opposite of what is required EXECUTE ('SET ANSI_NULLS OFF; SET ANSI_PADDING OFF; SET ANSI_WARNINGS OFF; SET ARITHABORT OFF; SET CONCAT_NULL_YIELDS_NULL OFF; SET NUMERIC_ROUNDABORT ON; SET QUOTED_IDENTIFIER OFF; INSERT INTO dbo.v1(c1) VALUES(1);');
but why should I have to?