ANSI_NULLS、QUOTED_IDENTIFIER

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

数据库服务器重启后，游戏登录提示“您输入的用户名不存在，请重新注册！”
重启前特意登录过平台，基本操作都正常。这之间并没对数据库做任何修改，怀疑是应用程序服务端的问题。开启跟踪监控登录过程，只跟踪到一个过程，手动执行过程也没有报错。将问题反馈给技术：
【2014/02/18 10:17:32】[proc_VerifyUserPWEx] ADO 错误：0x80040e14，INSERT 失败，因为下列 SET 选项的设置不正确: 'ANSI_NULLS, QUOTED_IDENTIFIER'。请确保 SET 选项正确无误，可以用于计算列上的索引视图和/或索引和/或筛选的索引和/或查询通知和/或 XML 数据类型方法和/或空间索引操作。。
查看存储过程定义(右击->编写存储过程脚本为->CREATE到->新查询编辑器窗口)，发现在过程的定义的前面，有SET ANSI_NULLS OFF、SET QUOTED_IDENTIFIER OFF语句。
开启跟踪，监控登录涉及代码，只看到执行proc_VerifyUserPWEx，Profiler的Error事件列(0 - OK)，将TextData复制到查询窗口执行没看到错误信息。依据经验登录正常的话会调用其他相关的存储过程，此时应该是在proc_VerifyUserPWEx过程出了异常。查看proc_VerifyUserPWEx过程的定义，发现过程使用了try...catch，但是在catch中直接return。这就解释了在查询窗口没有抛出错误信息的原因。
将过程代码放到测试用的DBA_Troubleshooting存储过程，并去掉try...catch，使用跟踪到的TextData参数，执行测试DBA_Troubleshooting，错误信息如下：

过程核心语句很简单：

SELECT colname FROM table1 WHERE...
IF @@ROWCOUNT=0
BEGIN
INSERT INTO table1...
INSERT INTO table2...
END

按照传入的参数并不会进入insert区域，为什么还要报insert失败呢？别忘了在过程定义的前面有SET ANSI_NULLS OFF、SET QUOTED_IDENTIFIER OFF语句，尝试分别将insert语句注释，定位在insert into table2失败，查看table2发现表上有一筛选索引(create index indexname on table2(col1,col2) include(col3) where col4 is null)。这个索引是春节前添加上去的(DDL数据库触发器记录)，当时没出现登录异常的情况。
问题变得清晰，存储过程Proc1定义使用SET ANSI_NULLS OFF、SET QUOTED_IDENTIFIER OFF，中间有insert into table...。后来在table表上创建一个筛选索引(where col is null)，程序依旧正常。直到某天重启数据库服务器/数据库服务，Proc1执行出现异常。
测试在查询窗口1按核心语句创建存储过程；在查询窗口2执行过程正常，然后创建筛选索引(ANSI_NULLS, QUOTED_IDENTIFIER需为ON)，执行过程正常。查看过程执行情况(dm_exec_procedure_stats)，显示重用了之前的执行计划。使用DBCC FREEPROCCACHE(plan_handle)清除原计划，再次执行过程报错！

/********查询窗口1********/
--创建测试表
CREATE TABLE Test_ANSI_NULLS(id INT,NAME VARCHAR(50))
--创建测试过程
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROC Pro_ANSI_NULLS
AS
SELECT TOP 1* FROM Test_ANSI_NULLS
IF @@ROWCOUNT=0
BEGIN
INSERT INTO
Test_ANSI_NULLS(id,NAME) SELECT TOP 1 object_id,NAME FROM sys.objects o ORDER BY NEWID()
END
GO/********查询窗口2********/
--执行三次成功
EXEC Pro_ANSI_NULLS
GO 3
--创建过滤索引
CREATE INDEX idx_name ON Test_ANSI_NULLS(NAME) WHERE NAME IS NOT NULL
--执行两次成功
EXEC Pro_ANSI_NULLS
GO 2
--过程执行情况
SELECT TOP 20 DB_NAME(a.database_id) DBname
,OBJECT_NAME(OBJECT_ID ,database_id) 'proc name'
,a.cached_time
,a.last_execution_time
,a.execution_count
,a.last_logical_reads
,a.total_logical_reads / a.execution_count avg_logical_reads
,a.total_logical_writes / a.execution_count avg_logical_writes
,a.total_physical_reads / a.execution_count avg_physical_reads
--,a.total_worker_time / a.execution_count AS avg_worker_time
,a.total_elapsed_time / a.execution_count avg_elapsed_time
--,b.text
,c.query_plan
,a.plan_handle
FROM sys.dm_exec_procedure_stats AS a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) c
where OBJECT_NAME(OBJECT_ID ,database_id) ='Pro_ANSI_NULLS'
ORDER BY
a.total_logical_reads/a.execution_count DESC
--清除过程缓存
DBCC FREEPROCCACHE(0x05000A004468463FB860AB15000000000000000000000000)
--再次执行过程出错
EXEC Pro_ANSI_NULLS
GO 2/********解决方案********/
--删除过滤索引
DROP INDEX idx_name ON Test_ANSI_NULLS
--修改过程定义
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC Pro_ANSI_NULLS
...
GO/********删除测试数据********/
DROP PROC Pro_ANSI_NULLS
DROP TABLE Test_ANSI_NULLS

注意，如果过程直接进入到insert into 区域，则在创建索引后，不需清除缓存，执行过程就报错。
解决方案：其一，删除过滤索引；其二，修改过程定义，尝试将其修改为SET ANSI_NULLS ON、SET QUOTED_IDENTIFIER ON。
ANSI_NULLS、QUOTED_IDENTIFIER的一些解释可参考链接。