Ansi_Nulls and Quoted_Identifier Killed Things. What Are They For

ANSI_NULLS and QUOTED_IDENTIFIER killed things. What are they for?

OK, from an application developer's point of view, here's what these settings do:

QUOTED_IDENTIFIER

This setting controls how quotation marks ".." are interpreted by the SQL compiler. When QUOTED_IDENTIFIER is ON then quotes are treated like brackets ([...]) and can be used to quote SQL object names like table names, column names, etc. When it is OFF (not recommended), then quotes are treated like apostrophes ('..') and can be used to quote text strings in SQL commands.

ANSI_NULLS

This setting controls what happens when you try to use any comparison operator other than IS on NULL. When it is ON, these comparisons follow the standard which says that comparing to NULL always fails (because it isn't a value, it's a Flag) and returns FALSE. When this setting is OFF (really not recommended) you can sucessfully treat it like a value and use =, <>, etc. on it and get back TRUE as appropiate.

The proper way to handle this is to instead use the IS (ColumnValue IS NULL ..).

CONCAT_NULL_YIELDS_NULL

This setting controls whether NULLs "Propogate" whn used in string expressions. When this setting is ON, it follows the standard and an expression like 'some string' + NULL .. always returns NULL. Thus, in a series of string concatenations, one NULL can cause the whole expression to return NULL. Turning this OFF (also, not recommended) will cause the NULLs to be treated like empty strings instead, so 'some string' + NULL just evaluates to 'some string'.

The proper way to handle this is with the COALESCE (or ISNULL) function: 'some string' + COALESCE(NULL, '') ...

Understanding QUOTED_IDENTIFIER

I saved the following command to a textfile, then executed it with SQLCMD:

SET QUOTED_IDENTIFIER ON
SET QUOTED_IDENTIFIER OFF

Checking in SQL profiler, SQLCMD -i <filename> connects with the following connection options on my system:

-- network protocol: LPC
set quoted_identifier on
...

however the following command issued by SQLCMD when it connects:

SET QUOTED_IDENTIFIER OFF SET TEXTSIZE 4096

and then it runs my script.

So, the answer to 2) is no - running a script with SQLCMD -i is not the same as executing from SSMS (with the default connections options). If a script requires QUOTED_IDENTIFIER ON, then you need to explicitly set it at the start if you're going to execute it this way.

Create a filtered index that will work with ANSI_NULLS OFF

There's no work around. See CREATE INDEX:

Required SET Options for Filtered Indexes

The SET options in the Required Value column are required whenever any of the following conditions occur:

...

• INSERT, UPDATE, DELETE, or MERGE operation modifies the data in a filtered index.

So the correct options have to be set when the UPDATE statement is issued. Your settings were already correct when you created the index (or you wouldn't have been allowed to)

Error while updating Database with mssql_query

You might have to explicitly change the settings by turning the settings on. You can do so by issuing the following query prior to the UPDATE statement:

SET 
  ANSI_NULLS, 
  QUOTED_IDENTIFIER, 
  CONCAT_NULL_YIELDS_NULL, 
  ANSI_WARNINGS, 
  ANSI_PADDING 
ON;

Should there be additional settings yielding errors, those might have to be changed as well.

See also: ANSWER: UPDATE failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'

What are Paris standards? Help me figure out what they mean here

As mentioned in the comments almost certainly a typo. Assuming they intended you to use PEAR standards

SET QUOTED IDENTIFIER should be ON when inserting a record

After a long struggle we were able to fix this problem. I just wanted to share the reason.

Our build team maintains a separate in-house tool to deploy scripts, which internally triggers the SQLCMD (shell) utility to execute T-SQL scripts in a db.

Here is the culprit: by default, QUOTED_IDENTIFIER is OFF when running in SQLCMD mode!

Every script run through this tool is created with QUOTED IDENTIFIER OFF. We are the only module which uses indexed views. All the remaining stories you know well in my previous posts :(

NOTE: I am going to vote everyone's post as useful.

Unable to Add Filtered Index but don't Understand Why

From the docs:

SET ANSI_NULLS must also be ON when you are creating or changing indexes on computed columns or indexed views. If SET ANSI_NULLS is OFF, any CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. SQL Server will return an error that lists all SET options that violate the required values. Also, when you execute a SELECT statement, if SET ANSI_NULLS is OFF, SQL Server will ignore the index values on computed columns or views and resolve the select operation as if there were no such indexes on the tables or views.

Get a unique current user, depending on what Online Platform they are using

It looks like your Online_Transactions table has multiple entries for your users?

You could use a CROSS APPLY to get the one row note the ORDER BY to get the latest one

SELECT DISTINCT
U.EMPLOYEE_TRACKING_ID,U.LAST_NAME, U.FIRST_NAME, U.EMAIL_ADDRESS, U.PERSON_TYPE, 
U.SERVICE_LINE, U.SUPERVISOR_NAME, U.OFFICE_LOCATION, U.OFFICE_CITY, U.OFFICE_STATE, 
U.OFFICE_COUNTRY, U.OFFICE_POSTAL_CODE, U.ACTUAL_TERMINATION_DATETIME,
T.FiscalPeriod, T.Role, T.Source, T.LogDate
FROM dbo.Users AS U
CROSS APPLY (
    SELECT TOP 1 O.FiscalPeriod, O.Role, O.Source, O.LogDate
    FROM dbo.Online_Transactions AS O
    WHERE O.TrackingId = U.EMPLOYEE_TRACKING_ID
    AND O.Source = 'AGOL'
    ORDER BY O.LogDate DESC
) AS T

This could also be done with an inner select sub query, but I like this syntax.
You might want to change CROSS APPLY for OUTER APPLY just to see the results, but I normally use CROSS APPLY which is like an INNER JOIN, in that it will only show users that have a match to an Online_Transaction.

---

Related Topics