聚集索引的困惑,欢迎讨论!
很久没来了,提问+散分.
今天遇到个问题,与聚集索引有关,不知道大家有没有遇到过这样的情况,请大家不吝赐教.
表中有一nvarchar列,含有数字和字符.需要将该列中的全数字行转换为数值后进行筛选.
转换的方法是先使用isnumeric()= 1来判断每行是否为数值,然后再使用cast()转换.
当表中没有聚集索引的时候可以正常转换,而一旦加了聚集索引,则转换会出错,错误信息为:
服务器: 消息 8114，级别 16，状态 5，行 2
将数据类型 nvarchar 转换为 float 时出错。
以下是测试代码,系统环境为:
简体中文SqlServer2000sp4 + 简体中文WindowsServer2003sp1
IF OBJECT_ID(N'tbTest') IS NOT NULL DROP TABLE tbTestGO----创建测试表CREATE TABLE tbTest(ID int,姓名 nvarchar(20),部门代码 nvarchar(10))insert into tbTestselect 1,N'张三',N'1000' union allselect 2,N'李四',N'1001' union allselect 3,N'王五',N'2001' union allselect 4,N'赵六',N'2002' union allselect 5,N'赵七',N'A3001' /*该行含有字符,并非全数字*/GO----查询(该查询能正常执行)SELECT * FROM tbTest WHERE isnumeric(部门代码) = 1 AND cast(部门代码 as float) > 1000/*结果ID 姓名 部门代码 ----------- -------------------- ---------- 2 李四 10013 王五 20014 赵六 2002*/ GO----！加入聚合索引IF INDEXPROPERTY(object_id(N'tbTest'),N'IX_tbTest','IndexID') IS NOT NULL DROP INDEX tbTest.IX_tbTestGOCREATE CLUSTERED INDEX IX_tbTest ON dbo.tbTest(ID)GO----查询(相同的查询代码,却因创建了聚合索引而抛出8114异常)SELECT * FROM tbTest WHERE isnumeric(部门代码) = 1 AND cast(部门代码 as float) > 1000/*结果ID 姓名 部门代码 ----------- -------------------- ---------- 2 李四 10013 王五 20014 赵六 2002服务器: 消息 8114，级别 16，状态 5，行 2将数据类型 nvarchar 转换为 float 时出错。*/
SELECT * FROM tbTest WHERE case when isnumeric(部门代码) = 1 then cast(部门代码 as float) else 0 end > 1000
[解决办法]
这个和SQL 生成本地代码有关吧,and 是同步执行时回报错，我在mssql 2005 下没加索引页报错
[解决办法]
在2005下，不加聚集索引也会出错
[解决办法]
看看
[解决办法]
05是报错，但不键unicode的nvarchar就不抱错
[解决办法]
学习..
[解决办法]
来学习
[解决办法]
请问为什么unicode的列，sql会先convert(float)?
[解决办法]
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
------------------------------------------------------------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ --------------------------------------------------------- ---------------------------------------------- ------------------------ ------------------------ ------------------------ ----------- ------------------------ ---------------------------------------------- -------- ------------------------------ -------- ------------------------
SELECT * FROM tbTest WHERE isnumeric(部门代码) = 1 AND cast(部门代码 as float) > 1000 4 1 0 NULL NULL 1 NULL 1.0031105 NULL NULL NULL 3.7663501E-2 NULL NULL SELECT 0 NULL
|--Clustered Index Scan(OBJECT:([CSDN_TEST].[dbo].[tbTest].[IX_tbTest]), WHERE:(Convert([tbTest].[部门代码])>1000 AND isnumeric(Convert([tbTest].[部门代码]))=1)) 4 3 1 Clustered Index Scan Clustered Index Scan OBJECT:([CSDN_TEST].[dbo].[tbTest].[IX_tbTest]), WHERE:(Convert([tbTest].[部门代码])>1000 AND isnumeric(Convert([tbTest].[部门代码]))=1) [tbTest].[部门代码], [tbTest].[姓名], [tbTest].[ID] 1.0031105 3.7578501E-2 8.3999999E-5 45 3.7662499E-2 [tbTest].[部门代码], [tbTest].[姓名], [tbTest].[ID] NULL PLAN_ROW 0 1.0
（所影响的行数为 2 行）
[解决办法]
学习+支持
[解决办法]
Mark 学习
[解决办法]
看完楼上的解释也就是 在特定情况下 查询计划顺序 不等于 语句书写顺序，刚也试过了把条件语句顺序调整后，执行计划不变
[解决办法]
如果是这样，那我们岂不是如履薄冰？
通常我们在创建并且关系的筛选条件时都是依据前一个条件成功了再判断下一个条件的，也就是说条件之间是有先后关系的。如果SQLSERVER这样做不是打着优化的旗号在胡作非为吗？第一次遇到这种情况，突然间感觉走在钢丝上。
------------------------------------------------
其实我觉得几呼所有语言都这相应的问题,或类似的处理
有时我们看表像,大多数情况下,我们理解的是对的, 但其实上结果并不是我们意料的.
举个例子
c# web page中
if(Request["xx"]!=null && Request["xx"]=="aa"){ //do something}
[解决办法]
学习，接分，，，
看星星，看钻钻，，，
[解决办法]
好多星星..钻钻..mart下学习.
[解决办法]
2005下,两种情况都会出这个错!
[解决办法]
在我看来，这样的查询一出来就表明存在问题，设计问题或者理解问题
SELECT * FROM tbTest WHERE isnumeric(部门代码) = 1 AND cast(部门代码 as float) > 1000
[解决办法]
2005的优化器比2000 的更好
比如在 2000里 LIKE '%D%' 查询用不到索引 但是2005 的就可以
[解决办法]
mark!!
[解决办法]
留名
[解决办法]
学了不少
[解决办法]
jf学习。。。
[解决办法]
让MSSQL提供一个关键字，可选择是否按原意优化或自行优化
嘿嘿嘿
[解决办法]
SQL codeWHERE isnumeric(部门代码) = 1 AND cast(部门代码 as float) > 1000
这行代码的词法含义（或者称为作者的原意）为：【如果】“部门代码”的内容为数字，【那么】就将“部门代码”转换成数值
---------------------
如果是这样的话，那么请这样写：
select * from (SELECT * FROM tbTest WHERE isnumeric(部门代码) = 1)a where cast(a.部门代码 as float) > 1000
很同意Haiwer 的观点：
-------------
在我看来，这样的查询一出来就表明存在问题，设计问题或者理解问题
SQL code
SELECT * FROM tbTest WHERE isnumeric(部门代码) = 1 AND cast(部门代码 as float) > 1000
[解决办法]
其实我觉得也不需要这么焦虑, 这种情况的出现确实是少数.
并不是一优化,所有的用户逻辑都会被改变.
只是自己以后要注意一些这样的语句, 过滤后的cast和convert, 还有由charindex取到位置再用substring取值的, 都有可能异常.
所有数据都能被最终操作(比如,你这里的cast,和我举的substring)所处理,那么无论它怎么优化,都是安全的.而大部分的语句,确实是这样,数据是能适应最终操作的.
以前其实也碰到过你说这个问题.后来就自己注意了一下.碰到你这种类似的要求,一般我都会直接写
where case when isnumeric(xx)=0 then 0 else xx end>1000
如果业务要求xx非numeric的也显示出来,我直接给值1000000.
或者是用nullif来处理.
[解决办法]
这贴真不错，听高手探讨问题就是不一样，这种帖子再多一点就好了
[解决办法]
So strong!
[解决办法]
其实没有什么好争论的, 看问题的角度不同, 自然观点不一样
如果你查询的数据就那么几万, 而且速度不太关心(或者说因为数据不多, 速度还可以), 很显然, 怎么写都可以, 你当然更在意是不是按照你写的意思
如果你经常从几百万的数据中查询, 而且条件还有那么几个, 如果没有自动优化, 想想你自己如何去解决效率问题吧, 我想没有自动优化, 你会被客户抱怨搞得头晕.
比如, 有三个查询条件, 每次都能保证有至少一个更能够筛选到最少的数据, 如果你自己去做优化, 你如何处理? 你怎么判断先用那个条件? 这些没有自动优化是很难想像的
从我自身的角度出发(我是DBA), 我当然更看重效率, 因为像楼主哪样的意图是可以解决的, 而且它不是不占很大比例, 大多数查询都没有楼主哪样的问题, 我不太可能为了少数查询放弃自动优化
[解决办法]
不好意思 我是新手 但是没有听够高手们的讲解
所以顶一下 让更多的高手来参与
呵呵 这样做有点自私了哦
[解决办法]
总结的好
[解决办法]
讨论到现在，也许楼主的这个问题无法得到准确或满意的答案
我想总应该有个结论：
就是查询优化器在何时进行优化，在何种情况下进行何种优化
[解决办法]
自己再总结一下：
1.MS SQL SERVER使用CBR进行优化，不一定非要按照子句的书写顺序执行；
2.MS SQL SERVER认为子句的AND前后的条件是并列关系，没有任何依赖或递进关系，因此查询优化器对任何条件的位置变换不会对查询结果的准确性造成影响。
所以，对于有依赖或递进关系的条件，必须进行特殊处理，如CASE WHEN或子查询。
不知道大家是怎么看的，欢迎继续讨论。还有一点，资料上并没有介绍怎样强制MS SQL SERVER使用RBO优化，或许根本就不支持RBO优化，也就没有这个开关吧。
----------------------
指点谈不上，一起讨论学习而已。
SQL Server默认采用的是CBO模式，想采用全局变量（开关）来决定实例或数据库的行为是做不到的，但是SQL Server还是支持RBO的。大家肯定用过Option关键字吧（Oracle里叫hint）？！这个东东就是SQL Server 对RBO的支持（只是粒度到了SQL语句级）。你可以利用这个东东指定你的SQL的执行计划，而不依赖于成本的变化。但是这个东东是一个双刃剑，如果没有专职DBA对SQL运行环境的变化进行定期跟踪，很容易出大乱子。使用Option进行优化，基本可以说是DBA的最后一招了！
[解决办法]
路过要看看
[解决办法]
再学习
[解决办法]
学习
[解决办法]
好贴留名。
[解决办法]
好貼!
留個腳印
[解决办法]
任何东西都有它的两面，如果在系统的初期预想到会有这样的情况存在，那不如在设计上解决，电脑还不不会像人脑那样分析问题的
[解决办法]
总结的好，，，
[解决办法]
好帖！学习一下！
对于查询优化器，在SQL 2005官方的资料里说这个查询优化器是整个SQL Server软件里面最复杂，最智能化的部分，对一个查询计划，它会根据非常多的条件去生成最优化的执行计划，所以建议说不要去强制优化器该怎么做，否则通常会得不偿失。
[解决办法]
Mark