SQL Server - Using Quoted Identifier ON, OFF, and Getdate ()

Question

I have a problem with my SQL script, please help me.

Example:

I have inserts:

INSERT INTO CUSTOMER (Code, Date) VALUES (1, GETDATE()); 

When I run this insert, reconfigures the following message:

"Msg 1934, level 16, state 1, HENRIQUE-PC server, line 5 INSERT failed because the following SET parameters are incorrect: 'QUOTED _IDENTIFIER . Make sure the SET parameters are correct for use with indexed views and / or indexes on calculated columns and / or filtered indexes and / or queries notificatio ns and / or XML data type methods and / or spatial index operations. ".

Now that I have used SET QUOTED_IDENTIFIER ON , my insert has completed successfully.

Example:

 SET QUOTED_IDENTIFIER OFF GO INSERT INTO CUSTOMER (Code, Date) VALUES (1, GETDATE()); SET QUOTED_IDENTIFIER ON GO 

(1 row (s) affected)

What is the relationship between GETDATE() and QUOTED IDENTIFIER ?

Why do I need to use QUOTED IDENTIFIER in this case?

I believe this is due to getdate. Why?

Thanks.

Enrique Melicio

Answer 1

Enriki

The reason you get this error is not related to GETDATE() , it is related to the indexes in the columns from the CUSTOMER table. This bit from the SQL Server 2008 SET Statements (Transact-SQL) document explains the problem in more detail:

When you create and manage indexes on computed or indexed columns, the SET ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS parameters must be set to ON. The NUMERIC_ROUNDABORT option must be set to OFF.

If any of these parameters is not set to the required values, INSERT, UPDATE, DELETE, DBCC CHECKDB and DBCC CHECKTABLE actions on indexed views or tables with indexes on calculated columns fail. SQL Server will raise an error by specifying all parameters that are incorrectly set. In addition, SQL Server will process SELECT statements on these tables or indexed views, as if there were no indexes on the computed columns or views.