聚集索引的困惑,欢迎讨论!

[hellowork]

很久没来了,提问+散分.
今天遇到个问题,与聚集索引有关,不知道大家有没有遇到过这样的情况,请大家不吝赐教.
表中有一nvarchar列,含有数字和字符.需要将该列中的全数字行转换为数值后进行筛选.
转换的方法是先使用isnumeric()= 1来判断每行是否为数值,然后再使用cast()转换.
当表中没有聚集索引的时候可以正常转换,而一旦加了聚集索引,则转换会出错,错误信息为:

服务器: 消息 8114，级别 16，状态 5，行 2
将数据类型 nvarchar 转换为 float 时出错。


以下是测试代码,系统环境为:
简体中文SqlServer2000sp4 + 简体中文WindowsServer2003sp1

IF OBJECT_ID(N'tbTest') IS NOT NULL
    DROP TABLE tbTest
GO
----创建测试表
CREATE TABLE tbTest(ID int,姓名 nvarchar(20),部门代码 nvarchar(10))
insert into tbTest
select 1,N'张三',N'1000' union all
select 2,N'李四',N'1001' union all
select 3,N'王五',N'2001' union all
select 4,N'赵六',N'2002' union all
select 5,N'赵七',N'A3001' 	/*该行含有字符,并非全数字*/
GO
----查询(该查询能正常执行)
SELECT * FROM tbTest WHERE isnumeric(部门代码) = 1 AND cast(部门代码 as float) > 1000

/*结果
ID          姓名                   部门代码       
----------- -------------------- ---------- 
2           李四                   1001
3           王五                   2001
4           赵六                   2002
*/ 

GO



----！加入聚合索引
IF INDEXPROPERTY(object_id(N'tbTest'),N'IX_tbTest','IndexID') IS NOT NULL
    DROP INDEX tbTest.IX_tbTest
GO
CREATE CLUSTERED INDEX IX_tbTest ON dbo.tbTest(ID)
GO
----查询(相同的查询代码,却因创建了聚合索引而抛出8114异常)
SELECT * FROM tbTest WHERE isnumeric(部门代码) = 1 AND cast(部门代码 as float) > 1000

/*结果
ID          姓名                   部门代码       
----------- -------------------- ---------- 
2           李四                   1001
3           王五                   2001
4           赵六                   2002

服务器: 消息 8114，级别 16，状态 5，行 2
将数据类型 nvarchar 转换为 float 时出错。
*/

[-狙击手-]

任何东西都有它的两面，如果在系统的初期预想到会有这样的情况存在，那不如在设计上解决，电脑还不不会像人脑那样分析问题的

[fcuandy]

继续学习

[dobear_0922]

总结的好，，，

[w2jc]

好帖！学习一下！

对于查询优化器，在SQL 2005官方的资料里说这个查询优化器是整个SQL Server软件里面最复杂，最智能化的部分，对一个查询计划，它会根据非常多的条件去生成最优化的执行计划，所以建议说不要去强制优化器该怎么做，否则通常会得不偿失。

[prcgolf]

up

[leihentulong]

学习

[ylm163net]

楼住的问题只能是楼主自己的问题,试问这样一个查询:
select * from Prodouct where Price<@Price and CrDate>@CrDate
其中 @Price @CrDate使用户通过界面输入的条件值,不让数据库优化,如果Prodouct记录很多,你会死得很惨

[zhou__zhou]

学习//高手

[稍微有点无敌]

学习了，这个帖子引出的问题还真不少啊

[lilingbill]

对于楼主这个问题，分2点回答。
1、出现楼主所说的这种上情况，主要是你加索引后是通过索引去找真实的物理记录。但索引的位置和物理记录的位置是不相同的。优化器会结合索引和你的查询条件，以及它的分析 结果得出一个最优化（一般情况下是这样）的执行计划。从而导致了先执行了你的转换操作（可能优化器判断结果为要执行转换的记录数比不的执行转换的记录数更少 或者 是建立的索引使得先执行了一条包含有字母的记录）。
2、对于优化器这种自作主张的行为，是可以控制的。在oracle中通过加强制的hints就可以了，但不知道sql server中是否可以这样！

[hwrt]

学习，学习再学习，学到能看懂上面！

[playwarcraft]

暈，居然沒發現這個帖子，虧大的

[kokyulei]

学习了，还真不是盖的！

[zhnzzy]

bu cuo

[yingwuhahahaha]

学习了,好多高手

[seai]

收藏了

[Quote=引用 67 楼 hellowork 的回复:]
感谢ashzs 和zjcxc 的热心关注和指点。

为了把问题搞清楚，查了一下有关MS SQLSERVER查询优化器的资料，资料写得很笼统，但至少能说明些问题，特摘要一下供大家参考：（以下为引用原文，原文中括号部分为我添加的注释）

查询优化是选择查询的执行规划（注：或称为执行计划）的过程。
查询优化器有两种类型：基于语法的（注：RBO，Rule-Based Optimizer，也有人称为基于规则的）和基于成本的（注：CBO，Cost-Based Opti…
[/Quote]

[seai]

收藏了

[Quote=引用 67 楼 hellowork 的回复:]
感谢ashzs 和zjcxc 的热心关注和指点。

为了把问题搞清楚，查了一下有关MS SQLSERVER查询优化器的资料，资料写得很笼统，但至少能说明些问题，特摘要一下供大家参考：（以下为引用原文，原文中括号部分为我添加的注释）

查询优化是选择查询的执行规划（注：或称为执行计划）的过程。
查询优化器有两种类型：基于语法的（注：RBO，Rule-Based Optimizer，也有人称为基于规则的）和基于成本的（注：CBO，Cost-Based Opti…
[/Quote]

[seai]

[Quote=引用 101 楼 seai 的回复:]
感谢ashzs 和zjcxc 的热心关注和指点。

为了把问题搞清楚，查了一下有关MS SQLSERVER查询优化器的资料，资料写得很笼统，但至少能说明些问题，特摘要一下供大家参考：（以下为引用原文，原文中括号部分为我添加的注释）

查询优化是选择查询的执行规划（注：或称为执行计划）的过程。
查询优化器有两种类型：基于语法的（注：RBO，Rule-Based Optimizer，也有人称为基于规则的）和基于成本的（注：CBO，Cost-Based Optimizer）。

MS SQL SERVER采用基于成本的查询优化器，所以保持表和索引中数据的当前分布统计是很重要的，否则SQL SERVER就不能智能地选择合适的执行规划。

1.基于语法的查询优化器。
基于语法的查询优化器完全根据查询的准确语法来选择规划。这是建立在查询中子句的顺序的基础上的。一旦选定了一个规划，该查询就将完全按照该规划执行，并且执行的顺序就是子句的顺序。基于语法的查询不需要保存分布统计，随着表的分布的改变，一段时间后，所选定的查询规则就可能不再有那么好的性能。
2.基于成本的查询优化器。
基于成本的查询优化器分析数据和索引列的分布统计，从而根据完成查询的成本来决定效率最高的执行规划。成本是根据资源利用率如CPU时间和I/O操作的次数等来测量的。响应时间也是影响成本的一个因素。SQLSERVER寻求能够以最快的速度把结果返回给用户并且不会导致其他方面性能下降的途径。因此，在具有多个处理器的系统中，查询处理器可能选择这样的查询规划，虽然它的总体成本比其他的执行规划要大一些，但是它可以更快地把结果返回给用户，并且不会降低其他方面的性能。
分布统计是从索引和表中的数据列的分布信息汇总出来的，并且由SQL SERVER自动在建立了索引的列上创建和维护。也可以选择把统计建立在没有索引的列上。
保持当前的分布统计是维持SQL SERVER的良好性能的关键。统计的更新方式有两种：如果数据已经存在于表中，就会在第一次创建索引的时候进行更新；另外一种方式是执行UPDATE STATISTICS命令进行更新。
基于成本的优化器将分析各种查询规划，并且作为其评价标准的信息，包括被请求的数据量以及处理过程花费的时间，具体表现在对CPU和内存的影响以及I/O操作的次数。
......（摘要结束）
---------------------------------------------------------------------------------------
看了上面摘要的内容，首先我明确了一个前提：MS SQL SERVER使用的是CBO，而不是RBO，也就是说作者提交的查询并不一定要按照WHERE子句中书写的顺序执行，而是按照效率最高的途径执行。

有了这个前提，还不足以来合理解释本例中出现的怪异的结果，还缺少一点至关重要的必要条件。
个人觉得这个必要条件就是：MS SQL SERVER会把WHERE子句中使用AND进行连接的条件平等对待，SQLSERVER认为AND前后的条件没有任何递进或继承关系（递进或继承关系是指：如果第一个条件为真，那么第二个条件才能继续，也就是说在书写顺序上，后一个依赖于前一个条件的成功，如果前一个条件返回假值则后一个不能继续，例如本例的类型转换），而是并列关系，完全可以按照优化的需要随意颠倒过来。

既然知道SQLSERVER是这样对待WHERE中的条件的，那么任何含有递进或继承关系的条件如果不进行特别处理，在此优化规则下都可能会出错，例如下面的列子：
表中有nvarchar列，保持的日期常量，用户在输入或修改的过程中很可能由于各种原因输入了合法日期以外的字符，当对该列进行日期处理时，无论是否创建了索引，都会导致错误：

SQL codeIF OBJECT_ID(N'tbTest') IS NOT NULL
DROP TABLE tbTest
GO
----创建测试表
CREATE TABLE tbTest(ID int,姓名 nvarchar(20),日期 nvarchar(20))
insert into tbTest
select 1,N'张三',N'2007-12-11' union all
select 2,N'李四',N'2007-12-12' union all
select 3,N'王五',N'2007-12-13' union all
select 4,N'赵六',N'2007-12-14' union all
select 5,N'赵七',N'A2007-12-15' /*该行含有字符,非法日期*/
GO

----查询1（对这种递进关系进行了CASE WHEN处理，查询成功）
SELECT * FROM tbTest WHERE CASE WHEN isdate(日期) = 1 THEN datepart(day,日期) ELSE NULL END > 11

----查询2(未作任何处理，该查询抛出异常)
SELECT * FROM tbTest WHERE isdate(日期) = 1 AND datepart(day,日期) > 11

/*结果2
ID 姓名 日期
----------- -------------------- --------------------
2 李四 2007-12-12
3 王五 2007-12-13
4 赵六 2007-12-14

服务器: 消息 241，级别 16，状态 1，行 3
从字符串转换为 datetime 时发生语法错误。
*/




自己再总结一下：
1.MS SQL SERVER使用CBR进行优化，不一定非要按照子句的书写顺序执行；
2.MS SQL SERVER认为子句的AND前后的条件是并列关系，没有任何依赖或递进关系，因此查询优化器对任何条件的位置变换不会对查询结果的准确性造成影响。
所以，对于有依赖或递进关系的条件，必须进行特殊处理，如CASE WHEN或子查询。

不知道大家是怎么看的，欢迎继续讨论。还有一点，资料上并没有介绍怎样强制MS SQL SERVER使用RBO优化，或许根本就不支持RBO优化，也就没有这个开关吧。
[/Quote]

[saynotono]

mark mark!

[JayPan2008]

[Quote=引用 20 楼 的回复:]

可以尝试换一种写法避开SQL的查询优化.
比如case when,或nullif和isnull的组合,不过索引将不被利用了.


SQL code
SELECT * FROM tbTest WHERE ISNULL(NULLIF(ISNUMERIC(部门代码),1),CAST(部门代码 AS FLOAT)) >1000
[/Quote]

使用 isnull where 后面的条件还是会自动优化 还有什么其他的会避开自动优化的？