SQL SERVER and SET ANSI_NULLS ON, SET QUOTED_IDENTIFIER ON

Question

So I have been blindly using ansi_nulls on, quoted_identifier on when creating scripts, because sqlserver generates them automatically when scripting objects..

I don't really have time to concern myself with such trivial non-sense :-) But i guess the questions have got to be asked.

Given that these are the recommended settings, is there an option that can be set (a) per database, and (b) per server, So that they are ALWAYS ON by default, rather than by individual scripts?

And if so, how / where can the current default settings be viewed?

Quito

learn.microsoft.com/en-us/sql/t-sql/statements/… – Lizarraga

Answer 1

Both are set at connection level, not database. If you don't specify them in your script, they will use the default connection settings. You can see these by right clicking your server in SSMS and selecting Properies. Go to the Connections Pane and there is a check box for ANSI NULL default On/Off. If neither are selected, the "default default" is ON.

You can also get the settings by using @@OPTIONS:

SELECT CASE @@OPTIONS & 1024 WHEN 0 THEN 'OFF' ELSE 'ON' END AS SET_ANSI_NULLS_ON,
       CASE @@OPTIONS & 2048 WHEN 0 THEN 'OFF' ELSE 'ON' END AS SET_ANSI_NULLS_OFF;

A full list of the bitwise values can be found here: Configure the user options Server Configuration Option

You can turn the ANSI NULLs on by default for connections by using:

EXEC sys.sp_configure N'user options', N'1024';
GO
RECONFIGURE;

Notion

I am still confused. I only knew about 'ANSI NULLS', but now I find two more similar (but independent) settings 'ANSI NULL Default On', 'ANSI NULL Default Off' Care to update your answer to explain the difference between the 3 ANSI settings? – Quito

Not sure what you're asking here, @Quito . ANSI NULLS is what is it; if it is on then a NULL is treated like a NULL, if not it isn't (So things like 1 <> NULL evaluate to true) . Those are the only 2 states for ANSI NULLS, on or off. There is no "3rd" option. – Notion

when i went to SSMS right click the Server, select Properties, Connections Pane, etc.. I see all the "Default connection options" with checkboxes. Instead of there being only ANSI NULLS with a checkbox, there is also ANSI NULL Default Off with a checkbox, and there is also ANSI NULL Default On with a checkbox (+ others ... quoted identifier, etc etc). So why have these 3 different "ANSI" related options independently ? what does each do ? – Quito

That's covered in the links above @Quito – Notion

Answer 2

When you wish to create a new SP using SQL Server Management Studio(SSMS) 2015 Express it generates a template similar to the below snip with the statements 'SET ANSI_NULLS ON','SET QUOTED_IDENTIFIER ON','SET NOCOUNT ON' by default:

1. SET ANSI_NULLS ON:

When ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name.According to ANSI(American National Standard Institute) any comparison or calculation performed with NULL is NULL. NULL is neither a string nor a number nor zero.It is simply NOT DEFINED.So, When I do SET ANSI_NULLS ON; I am simply telling SQL SERVER to follow the standard of ANSI.

When ANSI_NULLS is set to ON. ‘=’ operator is not able to identify NULL. This is the standard of ANSI that any comparison with NULL is always NULL. So you will never ever get a result for this. To find NULL in a column we use Keyword ‘IS NULL’.

2. SET QUOTED_IDENTIFIER ON:

In simple words, when you do SET QUOTED_IDENTIFIER ON you can use SQL identifier or reserved keywords in SQL as user-defined objects. It must be defined within double quotation marks & hence the name QUOTED_IDENTIFIER.

Consider a situation where you want your column name be ‘Identity’. But Identity is a keyword for SQL Server. How it will differentiate between a user referred Identitiy Or T-SQL referred Identity.This is where Quoted Idenfiers come in handy.

When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers. Therefore, quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be reserved keywords and can include characters not generally allowed in Transact-SQL identifiers.

For references kindly refer:

• SET ANSI_NULLS, NOCOUNT and QUOTED_IDENTIFIER.
• SET Statements (Transact-SQL)

Bogusz

This answers the question that let me here, but not the question the original poster asked. – Clifton

Answer 3

Modern SQL Server client APIs set both ANSI_NULLS ON and QUOTED_IDENTIFIER ON by default. So the options will be on by default and take precedence over database SET option settings and you don't need to include the SET statements in DDL scripts.

However, once gotcha is the SQLCMD utility, which sets QUOTED_IDENTIFIER OFF for backwards compatibility reasons. One needs to invoke SQLCMD with the -I argument to set QUOTED_IDENTIFIER ON.

Taxiplane

Does your reference to "modern SQL Server client APIs" include SSMS (management studio) ? and if so, from what versions onwards ? – Quito

@joedotnot, yes, SSMS uses SqlClient. SqlClient, ODBC, OLE DB, JDBC, et. al. connect with the settings on. DB-LIB (deprecated ca 2000) does not. – Taxiplane