Computed colums and slowness issue

Question

Hi,

My question is about computed columns and slowness issue.
Table has several computed columns some of them which are persistent and indexed.
Slowness issue occur inside the SP queries which uses this table even pulling from indexed fields.
I observed that SET QUOTED_IDENTIFIER ON / OFF state is affect the query performance.
My question is, should i open this parameter everywhere that uses the table? Do I need to open it wherever the table is selected, joined or updated?
I don't fully understand the effect of this parameter, I knew until now that this parameter is related to the handling of quotation marks.

Thanks

Answer 1

To start with, when working with indexed views, indexes on computed columns, filtered indexes and a few more features, there are a couple of settings that must be ON: ANSI_NULLS, ANSI_WARNINGS, ANSI_PADDING, CONCAT_NULL_YIELDS_NULL and QUOTED_IDENTIFIER. (Furthermore, NUMERIC_ROUNDABORT must be OFF). For all these five, the setting OFF is a pure legacy setting, and you would only use that setting with old code that has not been modernised. This has been the case since SQL 2000 was released.

One consequence of this is that when any of these settings are in the OFF position, indexes on computed columns are ignored, which could explain why your queries run slower when QUOTED_IDENTIFIER is OFF.

QUOTED_IDENTIFIER controls how the double quote (") is interpreted. With the default setting ON, "" quotes identifiers on equal footing with the brackets. These statements are equivalent:

   CREATE TABLE "My Table"(a int NOT NULL)  
   CREATE TABLE [My Table](a int NOT NULL)  

The brackets are proprietary to Microsoft, whereas the double quotes are the ANSI standard.

When QUOTED_IDENTIFIER is OFF, the double quote is a string delimiter on equal footing with the single quote('). Look at this script:

   SELECT "name" FROM sys.objects WHERE object_id = 8  -- returns "sysfiles1"  
   go  
   SET QUOTED_IDENTIFIER OFF  
   go  
   SELECT "name" FROM sys.objects WHERE object_id = 8 -- returns "name".  
   go  
   SET QUOTED_IDENTIFIER ON  

The default for QUOTED_IDENTIFIER is ON, but there are a few exceptions. The following environments, by default forces QUOTED_IDENTIFIER to OFF:

• T-SQL job step SQL Server Agent.
• SQLCMD.
• BCP.
  For SQLCMD you can override the behaviour with the -I option, and for BCP you can use the -q option. For Agent, you need to include a SET QUOTED_IDENTIFIER ON command in your batch.

There is one more important thing to know about QUOTED_IDENTIFIER when it comes to stored procedures etc. When you run a stored procedure, the run-time setting is of no importance. Instead the setting is saved with the procedure. (Which is quite understandable, since depending on the meaning of the double quote, the code may not be syntactically correct.)

You can review if you have any modules which has been created with QUOTED_IDENTIFIER OFF:

   SELECT s.name, o.name   
   FROM   sys.sql_modules sm  
   JOIN   sys.objects o ON sm.object_id = o.object_id  
   JOIN   sys.schemas s ON s.schema_id = o.schema_id  
   WHERE  (sm.uses_quoted_identifier = 0 or  
           sm.uses_ansi_nulls = 0)  
     AND  o.type NOT IN ('R', 'D')  

(The query also covers the setting ANSI_NULLS which is also saved with the module.) This query should ideally not return any rows.

I should add the caveat that if you have an old system that started it's life on SQL 6.x or earlier where double quote always was a string delimiter, there may still be plenty of code with double quote used in the old way.

Answer 2

Hi，@Muzaffer

Welcome to Microsoft T-SQL Q&A Forum!

SET QUOTED_IDENTIFIER must be ON when creating or changing an index on a computed column or indexed view. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements will fail on tables with indexes on computed columns or on tables with indexed views.

SET QUOTED_IDENTIFIER takes effect during Transact-SQL parsing and only affects parsing, not query optimization or query execution. So your performance problems will not be triggered by this option being turned on. For more information on the effect of turning this option on, please visit the link.

The performance degradation is caused by many factors, you might as well try creating an index on a persistent column and see this link for an explanation.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.