Sql-server – Strange behaviours with SET options in SSMS

configurationmaterialized-viewsql serversql server 2014

I am investigating the effect of various SET options on the creation of indexed views on SQL Server 2014 CU1. The following code:

  1. ensures that the SET options are as they should be
  2. creates a table
  3. creates a view based on the table
  4. creates an index on the view
  5. 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?

Best Answer

Among the SET statements in your script, SET QUOTED_IDENTIFIER is special in that it is processed at parse time rather than at execute time.

From SET Statements (Transact-SQL) (emphasis mine):

Considerations When You Use the SET Statements

  • All SET statements are implemented at execute or run time, except for SET FIPS_FLAGGER, SET OFFSETS, SET PARSEONLY, and SET QUOTED_IDENTIFIER. These statements are implemented at parse time.

Confirmed in SET QUOTED_IDENTIFIER (Transact-SQL):

Remarks

[…]

SET QUOTED_IDENTIFIER is set at parse time. […]

So, in your case QUOTED_IDENTIFIER gets reset at parse time and is, therefore, OFF by the time CREATE INDEX is executed. The engine complains about it as expected.

To resolve this, you don't have to resort to dynamic SQL, it is enough to put a GO line just after the CREATE INDEX statement to make it a different batch from the subsequent SETs, which would cause it to be parsed separately from them:

…

CREATE UNIQUE CLUSTERED INDEX i1 ON dbo.v1(c1);

GO

-- 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;

…

Note, however, that SET Statements (Transact-SQL) also includes the following (emphasis mine):

Considerations When You Use the SET Statements

[…]

  • When you are creating and manipulating indexes on computed columns or indexed views, the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option NUMERIC_ROUNDABORT must be set to OFF.

    If any one of these options is not set to the required values, INSERT, UPDATE, DELETE, DBCC CHECKDB and DBCC CHECKTABLE actions on indexed views or tables with indexes on computed columns will fail. SQL Server will raise an error listing all the options that are incorrectly set. Also, SQL Server will process SELECT statements on these tables or indexed views as if the indexes on computed columns or on the views do not exist.

Which means that the uncommented SET statements would still break your script, only not at CREATE INDEX but at INSERT.

Related Question