Set ANSI_NULLS on SET QUOTED_IDENTIFIER What does it mean SQL Server 2005 2008

Original: http://www.cnblogs.com/ForFreeDom/archive/2009/10/16/1584680.html

In a sqlserver2005 or SQL2008 database project, creating a view or stored procedure often has the following opening statement:

SET ansi_nulls ongoset quoted_identifier Ongo ......... ................. Goset ANSI_NULLS Offgoset quoted_identifier OFFGO

　　

These are SQL-92 SET statements that allow SQL Server 2000/2005 to comply with SQL-92 rules.
When SET QUOTED_IDENTIFIER is on, identifiers can be delimited by double quotation marks, and literals must be separated by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers are not quoted and must conform to all Transact-SQL identifier rules.
The SQL-92 standard requires that the value be FALSE when the null value equals (=) or does not equal (<>) is compared. When SET Ansi_nulls is on, a SELECT statement that uses WHERE COLUMN_NAME = NULL still returns 0 rows even if column_name contains null values. SELECT statements that use WHERE column_name <> NULL will return 0 rows even if the column_name contains non-null values.
When SET Ansi_nulls is OFF, the Equals (=) and not Equals (<>) comparison operators do not comply with the SQL-92 standard. Use the WHERE COLUMN_NAME = null SELECT statement to return rows that contain null values in column_name. Use the WHERE column_name <> NULL SELECT statement to return rows that contain non-null values in the column. Also, use the SELECT statement in WHERE column_name <> xyz_value to return all rows that are not xyz_value and not null.

SQL Server Books Online there is a code example in Ansi_nulls that gives an intuitive effect, and I added the output hint as follows:

--Create table t1 and insert values. CREATE TABLE T1 (a INT NULL) insert into T1 values (NULL) insert to T1 values (0) insert into T1 values (1) go--Print Messa GE and perform SELECT statements. PRINT ' testing default setting ' DECLARE @varname intselect @varname = nullselect * from t1 WHERE a = @varname--Empty SEL result ECT * from T1 where a <> @varname-the result is empty select * from T1 where A is NULL-the result is nullgo--SET ansi_nulls to O N and test. PRINT ' testing ansi_nulls on ' SET ansi_nulls ongodeclare @varname intselect @varname = nullselect * from t1 WHERE a = @varn Ame-The result is empty select * from T1 where a <> @varname--The result is empty select * from T1 WHERE A is NULL-the result is nullgo--SE T ansi_nulls to OFF and test.  PRINT ' testing set ANSI_NULLS OFF ' Set ansi_nulls offgodeclare @varname intselect @varname = NULL SELECT * from T1 WHERE a = @varname-Nullselect * from T1 where a <> @varname-results are 0,1select * from T1 where A is NULL--knot The fruit is nullgo--Drop table t1. DROP TABLET1 

Judging from the running effect of the above code:

① when SET ANSI_NULLS on, the result is null when the query condition is =null or <> null, and NULL is displayed if it is null.
② when ANSI_NULLS is not set, the effect is the same as set ANSI_NULLS on, where one of the requirements of SQL-92 "SQL-92 standard requires a value equal to (=) or Not equal to (<>) to be compared to a null value of FALSE, is the default value. "Conflict oh?!
How do you understand this problem?
In SQL Server Books Online, there is an answer in ANSI_NULLS's Knowledge Document: "The SQL Server ODBC driver and the Microsoft OLE DB provider for SQL Server automatically set ANSI_NULLS to on when connected." This setting can be configured in ODBC data sources, ODBC connection attributes, or OLE DB connection properties that are set in the application before they are connected to SQL Server. ”

PS: Actually, in practice, we do not consider whether ANSI_NULLS is set to ON or false, our handling of NULL values is NULL or is not NULL

Set ANSI_NULLS on SET QUOTED_IDENTIFIER What does it mean SQL Server 2005 2008