这是 SQL-92 设置语句,使 SQL Server 遵从 SQL-92 规则。
当 SET ANSI_NULLS 为 ON 时,等于 (=) 和不等于 (<>) 比较运算符遵从 SQL-92 标准,对NULL值的操作只能用:
WHERE Column IS NULL / WHERE Column IS NOT NULL
当 SET ANSI_NULLS 为 OFF 时,对NULL值的操作可以用:
WHERE Column = NULL / WHERE Column <> NULL
WHERE Column IS NULL / WHERE Column IS NOT NULL
使用 WHERE Column <> Value 时返回所有不等于 Value 也不等于 NULL 的行;
SET ANSI_NULLS ON; WITH T1 AS (SELECT 1 AS ID UNION ALL SELECT NULL UNION ALL SELECT 2) SELECT * FROM T1 WHERE ID IS NULL; SET ANSI_NULLS OFF; WITH T1 AS (SELECT 1 AS ID UNION ALL SELECT NULL UNION ALL SELECT 2) SELECT * FROM T1 WHERE ID = NULL; SET ANSI_NULLS OFF; WITH T1 AS (SELECT 1 AS ID UNION ALL SELECT NULL UNION ALL SELECT 2) SELECT * FROM T1 WHERE ID <> 1;
关于NULL的延伸:
1. 对应聚合函数COUNT,只有SELECT COUNT(*)和SELECT COUNT(1) 会将为NULL的行计算在内,SELECT COUNT(ID)会忽略ID列为NULL的行 2. 对应除COUNT外的其他聚合函数,计算时不考虑为NULL的行 3. 对应CHECK CONSTRAINT,如果列允许为NULL的条件下,为NULL的行将不受CHEKC CONSTRAINT的限制,例如有CHECK CONSTRAINT条件为C1>10,对C1列插入NULL值或更新为NULL不会违反CHECK CONSTRAINT. 4. 在GROUP BY , Distinct 的时候,会将所有NULL归为一组
当 SET QUOTED_IDENTIFIER 为 ON 时,双引号可以代替标识符,单引号表示文本。 当 SET QUOTED_IDENTIFIER 为 OFF 时,标识符不可能用双引号代替,单引号,双引号都表示文本。且必须符合所有 Transact-SQL 标识符规则。
-- OFF 时, 不能用引号代替标示符: SET QUOTED_IDENTIFIER OFF CREATE TABLE "table name" ("identity" INT NOT NULL, "order" VARCHAR(100) NOT NULL); -- ON时, 双引号可以代替标示符[]: SET QUOTED_IDENTIFIER ON; CREATE TABLE "table name" ("identity" INT NOT NULL, "order" VARCHAR(100) NOT NULL); GO SELECT "identity","order" FROM "table name" ORDER BY "order"; SET QUOTED_IDENTIFIER OFF; INSERT INTO dbo.[table name] VALUES (1, "'Text in single quotes'"); INSERT INTO dbo.[table name] VALUES (2, '''Text in single quotes'''); INSERT INTO dbo.[table name] VALUES (3, 'Text with 2 '''' single quotes'); INSERT INTO dbo.[table name] VALUES (4, '"Text in double quotes"'); INSERT INTO dbo.[table name] VALUES (5, """Text in double quotes"""); INSERT INTO dbo.[table name] VALUES (6, "Text with 2 """" double quotes"); INSERT INTO dbo.[table name] VALUES (7, 'Text with a single '' quote'); GO --ON时, 双引号不能用于字符串: SET QUOTED_IDENTIFIER ON; INSERT INTO dbo.[table name] VALUES (8, """Text in double quotes"""); SELECT [identity], [order] FROM dbo.[table name]; DROP TABLE dbo.[table name];
参考文档:
http://www.cnblogs.com/TeyGao/p/3521657.html
https://msdn.microsoft.com/zh-cn/library/ms174393.aspx
http://flandycheng.blog.51cto.com/855176/280179