[怎樣處理]SQL2008、SQL2005類型判斷出錯

Question

use Tempdb
go
--在SQL2005和SQL2008時，都會出現set nocount on ;
if object_id('Tempdb..#A') is not null
drop table #Acreate table #A
(
A_ID int  not null ,
Type nvarchar(20)
)if object_id('Tempdb..#B') is not null
drop table #Bcreate table #B
(
A_ID int not null,
Value nvarchar(4000),
Num int--無意義列
)
insert #A select 1,N'A'
insert #A select 2,N'A'insert #B select 3,'Error',0--類型非字符/*
#A:
A_ID        Type
----------- --------------------
1           A
2           A#B:
A_ID        Value                Num
----------- -------------------- -----------
3           Error                0
*/--轉換后出錯時出錯,不成立沒結果集
select 
cast(b.value as bigint) as [轉換后出錯]
from #A a 
inner join #B b on a.A_Id=b.A_Id
/*
訊息 8114，層級 16，狀態 5，行 28
Error converting data type nvarchar to bigint.*/insert #B select 1,10,0--新增一條后，影響引擎類型判斷以下不會錯
go
select 
cast(b.value as bigint) as [轉換后出錯]
from #A a 
inner join #B b on a.A_Id=b.A_Id--怎樣處理方法1(用表提示處理,改變數據庫引擎的執行順序)
--LOOP | HASH | MERGE | REMOTE delete #B where isnumeric(Value)=1--刪除新增記錄
select 
cast(b.value as int) as [轉換后正常]
from #A a 
inner HASH join #B b on a.A_Id=b.A_Id
go
--新增20條數據,數據量記錄數對類型判斷有影響.declare @i int
set @i=0
while @i<10
begin
insert #B select 3,'Error'+rtrim(@i),2--類型非字符
set @i=@i+1
end
go
--force order用提示強制執行順序select 
cast(b.value as int) as [轉換后正常]
from 
#A a
Cross Apply
(select top 1 Value from #B where A_ID=a.A_ID order by Num asc)b
option(force order)
drop table #a,#b
http://hi.csdn.net/link.php?url=http://blog.csdn.net%2Froy_88%2Farchive%2F2010%2F01%2F04%2F5128737.aspx

Answer 1

SQL2005與SQL2008的引擎區別，生成數據的方式都影響執行計劃
以下引用MSDN上網友提出的問題，看了一下SQL05與SQL08的順序上有區別
SQL2005正常，SQL2008出錯
需要用force order處理begin tran
create table #A(AID int primary key,ItemType int)
create table #B(A_ID int,MyVal varchar(200),Sort int)insert #A
select 37,0 union all select 38,0 union all select 39,0 union all select 40,0 union all select 41,0 union all select 42,0 union all select 43,0 union all select 81,0 union all select 82,0 union all select 83,0 union all select 84,0 union all select 85,0 union all select 86,0 union all select 87,0 union all select 88,0 union all select 89,0 union all select 90,0 union all select 91,0 union all select 92,0 union all select 93,0 union all select 94,0 union all select 95,0 union all select 96,0 union all select 97,0 union all select 98,0 union all select 99,0 union all select 100,0 union all select 101,0 union all select 102,0 union all select 103,0 union all select 104,0 union all select 105,0 union all select 106,0 union all select 107,0 union all select 108,0 union all select 109,0 union all select 110,0 union all select 111,0 union all select 112,0 union all select 113,0 union all select 114,0 union all select 115,0 union all select 116,0 union all select 117,0 union all select 118,0 union all select 119,0 union all select 120,0 union all select 121,0 union all select 122,0 union all select 123,0 union all select 124,0 union all select 125,0 union all select 126,0 union all select 127,0 union all select 128,0 union all select 129,0 union all select 130,0 union all select 131,0 union all select 132,0 union all select 133,0 union all select 134,0 union all select 135,0 union all select 136,0 union all select 137,0 union all select 138,0 union all select 139,1

Answer 2

insert #B select 37,'1',0 union all select 37,'1',41 union all select 37,'1',42 union all select 37,'1',43 union all select 37,'1',44 union all select 38,'1',0 union all select 38,'1',41 union all select 38,'1',42 union all select 38,'1',43 union all select 38,'1',44 union all select 39,'1',0 union all select 39,'1',41 union all select 39,'1',42 union all select 39,'1',43 union all select 39,'1',44 union all select 40,'1',0 union all select 40,'1',41 union all select 40,'1',42 union all select 40,'1',43 union all select 40,'1',44 union all select 41,'1',0 union all select 41,'1',41 union all select 41,'1',42 union all select 41,'1',43 union all select 41,'1',44 union all select 42,'1',0 union all select 42,'1',41 union all select 42,'1',42 union all select 42,'1',43 union all select 42,'1',44 union all select 43,'1',0 union all select 43,'1',41 union all select 43,'1',42 union all select 43,'1',43 union all select 43,'1',44 union all select 81,'1',0 union all select 81,'1',41 union all select 81,'1',42 union all select 81,'1',43 union all select 81,'1',44 union all select 82,'1',0 union all select 82,'1',41 union all select 82,'1',42 union all select 82,'1',43 union all select 82,'1',44 union all select 83,'1',0 union all select 83,'1',41 union all select 83,'1',42 union all select 83,'1',43 union all select 83,'1',44 union all select 84,'1',0 union all select 84,'1',41 union all select 84,'1',42 union all select 84,'1',43 union all select 84,'1',44 union all select 85,'1',0 union all select 85,'1',41 union all select 85,'1',42 union all select 85,'1',43 union all select 85,'1',44 union all select 86,'1',0 union all select 86,'1',41 union all select 86,'1',42 union all select 86,'1',43 union all select 86,'1',44 union all select 87,'1',0 union all select 87,'1',41 union all select 87,'1',42 union all select 87,'1',43 union all select 87,'1',44 union all select 88,'1',0 union all select 88,'1',41 union all select 88,'1',42 union all select 88,'1',43 union all select 88,'1',44 union all select 89,'1',0 union all select 89,'1',41 union all select 89,'1',42 union all select 89,'1',43 union all select 89,'1',44 union all select 90,'1',0 union all select 90,'1',41 union all select 90,'1',42 union all select 90,'1',43 union all select 90,'1',44 union all select 91,'1',0 union all select 91,'1',41 union all select 91,'1',42 union all select 91,'1',43 union all select 91,'1',44 union all select 92,'1',0 union all select 92,'1',41 union all select 92,'1',42 union all select 92,'1',43 union all select 92,'1',44 union all select 93,'1',0 union all select 93,'1',41 union all select 93,'1',42 union all select 93,'1',43 union all select 93,'1',44 union all select 94,'1',0 union all select 94,'1',41 union all select 94,'1',42 union all select 94,'1',43 union all select 94,'1',44 union all select 95,'1',0 union all select 95,'1',41 union all select 95,'1',42 union all select 95,'1',43 union all select 95,'1',44 union all select 96,'1',0 union all select 96,'1',41 union all select 96,'1',42 union all select 96,'1',43 union all select 96,'1',44 union all select 97,'1',0 union all select 97,'1',41 union all select 97,'1',42 union all select 97,'1',43 union all select 97,'1',44 union all select 98,'1',0 union all select 98,'1',41 union all select 98,'1',42 union all select 98,'1',43 union all select 98,'1',44 union all select 99,'1',0 union all select 99,'1',41 union all select 99,'1',42 union all select 99,'1',43 union all select 99,'1',44 union all select 100,'1',0 union all select 100,'1',41 union all select 100,'1',42 union all select 100,'1',43 union all select 100,'1',44 union all select 101,'1',0 union all select 101,'1',41 union all select 101,'1',42 union all select 101,'1',43 union all select 101,'1',44 union all select 102,'1',0 union all select 102,'1',41 union all select 102,'1',42 union all select 102,'1',43 union all select 102,'1',44 union all select 103,'1',0 union all select 103,'1',41 union all select 103,'1',42 union all select 103,'1',43 union all select 103,'1',44 union all select 104,'1',0 union all select 104,'1',41 union all select 104,'1',42 union all select 104,'1',43 union all select 104,'1',44 union all select 105,'1',0 union all select 105,'1',41 union all select 105,'1',42 union all select 105,'1',43 union all select 105,'1',44 union all select 106,'1',0 union all select 106,'1',41 union all select 106,'1',42 union all select 106,'1',43 union all select 106,'1',44 union all select 107,'1',0 union all select 107,'1',41 union all select 107,'1',42 union all select 107,'1',43 union all select 107,'1',44 union all select 108,'1',0 union all select 108,'1',41 union all select 108,'1',42 union all select 108,'1',43 union all select 108,'1',44 union all select 109,'1',0 union all select 109,'1',41 union all select 109,'1',42 union all select 109,'1',43 union all select 109,'1',44 union all select 110,'1',0 union all select 110,'1',41 union all select 110,'1',42 union all select 110,'1',43 union all select 110,'1',44 union all select 111,'1',0 union all select 111,'1',41 union all select 111,'1',42 union all select 111,'1',43 union all select 111,'1',44 union all select 112,'1',0 union all select 112,'1',41 union all select 112,'1',42 union all select 112,'1',43 union all select 112,'1',44 union all select 113,'1',0 union all select 113,'1',41 union all select 113,'1',42 union all select 113,'1',43 union all select 113,'1',44 union all select 114,'1',0 union all select 114,'1',41 union all select 114,'1',42 union all select 114,'1',43 union all select 114,'1',44 union all select 115,'1',0 union all select 115,'1',41 union all select 115,'1',42 union all select 115,'1',43 union all select 115,'1',44 union all select 116,'1',0 union all select 116,'1',41 union all select 116,'1',42 union all select 116,'1',43 union all select 116,'1',44 union all select 117,'1',0 union all select 117,'1',41 union all select 117,'1',42 union all select 117,'1',43 union all select 117,'1',44 union all select 118,'1',0 union all select 118,'1',41 union all select 118,'1',42 union all select 118,'1',43 union all select 118,'1',44 union all select 119,'1',0 union all select 119,'1',41 union all select 119,'1',42 union all select 119,'1',43 union all select 119,'1',44 union all select 120,'1',0 union all select 120,'1',41 union all select 120,'1',42 union all select 120,'1',43 union all select 120,'1',44 union all select 121,'1',0 union all select 121,'1',41 union all select 121,'1',42 union all select 121,'1',43 union all select 121,'1',44 union all select 122,'1',0 union all select 122,'1',41 union all select 122,'1',42 union all select 122,'1',43 union all select 122,'1',44 union all select 123,'1',0 union all select 123,'1',41 union all select 123,'1',42 union all select 123,'1',43 union all select 123,'1',44 union all select 124,'1',0 union all select 124,'1',41 union all select 124,'1',42 union all select 124,'1',43 union all select 124,'1',44 union all select 125,'1',0 union all select 125,'1',41 union all select 125,'1',42 union all select 125,'1',43 union all select 125,'1',44 union all select 126,'1',0 union all select 126,'1',41 union all select 126,'1',42 union all select 126,'1',43 union all select 126,'1',44 union all select 127,'1',0 union all select 127,'1',41 union all select 127,'1',42 union all select 127,'1',43 union all select 127,'1',44 union all select 128,'1',0 union all select 128,'1',41 union all select 128,'1',42 union all select 128,'1',43 union all select 128,'1',44 union all select 129,'1',0 union all select 129,'1',41 union all select 129,'1',42 union all select 129,'1',43 union all select 129,'1',44 union all select 130,'1',0 union all select 130,'1',41 union all select 130,'1',42 union all select 130,'1',43 union all select 130,'1',44 union all select 131,'1',0 union all select 131,'1',41 union all select 131,'1',42 union all select 131,'1',43 union all select 131,'1',44 union all select 132,'1',0 union all select 132,'1',41 union all select 132,'1',42 union all select 132,'1',43 union all select 132,'1',44 union all select 133,'1',0 union all select 133,'1',41 union all select 133,'1',42 union all select 133,'1',43 union all select 133,'1',44 union all select 134,'1',0 union all select 134,'1',41 union all select 134,'1',42 union all select 134,'1',43 union all select 134,'1',44 union all select 135,'1',0 union all select 135,'1',41 union all select 135,'1',42 union all select 135,'1',43 union all select 135,'1',44 union all select 136,'1',0 union all select 136,'1',41 union all select 136,'1',42 union all select 136,'1',43 union all select 136,'1',44 union all select 137,'1',0 union all select 137,'1',41 union all select 137,'1',42 union all select 137,'1',43 union all select 137,'1',44 union all select 138,'1',0 union all select 138,'1',41 union all select 138,'1',42 union all select 138,'1',43 union all select 138,'1',44 union all select 139,'1.1,2.1',0 union all select 139,'1.1,2.1',41 union all select 139,'1.1,2.1',42
select
cast(t.MyVal as decimal(16,2))
from #A a
cross apply
(
 select top 1 b.MyVal from #B b
 where b.A_ID=a.AID
 order by b.Sort desc
)t
where a.ItemType=0
drop table #A
drop table #B
rollback

Answer 3

大家可以測測，以上在SQL05正常，在SQL08出錯改變生成數據的方式，又測不出來

Answer 4

又是sql所谓的优化引起的问题么？
学习

Answer 5

cast(t.MyVal as decimal(16,2))运算即可以放在join之前，也可以放在join之後，这个优化器说了算，总之我是不会写类似没有保障的语句~:)PS:这样给一个固定的逻辑不就可以了吗? select 
    cast(b.value as bigint) as [正常轉換]
from #A a 
    inner join 
(
SELECT * FROM #B
WHERE ISNUMERIC(value)=1
) b on a.A_Id=b.A_Id

Answer 6

拿以上例子測測你就會發現，不是條件位置可解決]
SQL08環境上測select
cast(t.MyVal as decimal(16,2))
from #A a
cross apply
(
 select top 1 b.MyVal from #B b
 where b.A_ID=a.AID and ISNUMERIC(b.MyVal)=1 --加上你說的條件
 order by b.Sort desc
)t
where a.ItemType=0

Answer 7

这种情况又不一样了。sql一样可以先执行join再筛选isnumeric=1。CREATE INDEX ix_t ON #B(sort)
GOselect
cast(t.MyVal as decimal(16,2))
from #A a
cross apply
(
 select top 1 b.MyVal from 
 (
  SELECT * FROM #B WHERE ISNUMERIC(b.MyVal)=1 
  )  b
 where b.A_ID=a.AID and 
 order by b.Sort desc
)t
where a.ItemType=0

Answer 8

这里还是要借助index来改变execution plan，不理想。改成independent sub query 才好. 

Answer 9

樓上兄弟，注意不要去改改環境。改變方法太多。
這是在比較引擎的區別變化---以下這樣的寫法效率低,不推薦加上條件
(
  SELECT * FROM #B WHERE ISNUMERIC(b.MyVal)=1
  )  b 通常情況以上寫法改為SQL2000寫法時，效率高

Answer 10

在14、15樓上列出的例子，是引用MSDN上網上提出的問題很有意思。
數據不變，改變一下生成數據的方式，引擎的執行順序都會改變，測不出來所以當升級SQL2008時，要注意在SQL2005上的寫法，有沒有類似情況

Answer 11

[怎樣處理]SQL2008、SQL2005類型判斷出錯
你的主题不是"怎样处理"类型判出错么?其实我想说的是，为何使用Query hint去干涉SQL优化器的行为，直接给一个固定的语义给它不好吗? 

Answer 12

這也是一种方法
我個人比較喜歡用SQL2000寫法的處理，用Apply時，曾比較過在多數據情況下的效率都低於SQL2000的寫法 Apply 效率在很多情況下等同於 select col=(select top Col from t where) from t a

Answer 13

Garnett_KG 兄弟提出不同見解，10分 分派給你了

Answer 14

ISDATE()好像也有这个问题,判断与执行的顺序,学习

Answer 15

语句不变，引擎在不断升级，想把一句话写成永久固定语义比较难吧。类型判断isdate,isnumeric在复杂的句子里，真正执行的位置经常飘忽不定，
我原来有个解决方案就是，用isnumeric先把正确数据提出来暂存至临时表，以后就用这个临时表了。用起来感觉还凑合，不用在句子里费劲调整isnumeric的位置，也基本不用担心引擎的升级。
而且执行效率往往不比一个过于复杂的句子低。

Answer 16

1.同意31楼的看法，不到万不得已不用Query/Table hint
2.遇到过类似情况（转Datetime）
3.
select
CASE WHEN ISNUMERIC(t.MyVal) = 1 THEN cast(t.MyVal as decimal(16,2)) ELSE NULL END
from #A a
cross apply
(
 select top 1 b.MyVal from #B b
 where b.A_ID=a.AID
 order by b.Sort desc
)t
where a.ItemType=0

Answer 17

水牛帮我看帖 
http://topic.csdn.net/u/20091230/15/b44c5a6a-710e-4ca0-a3d4-5856a76025c9.html

Answer 18

哈哈，下次加多點分。年后再不斷推出整理貼給大家分享.主要針對SQL08\05環境下的XML、引擎、CLR方面、SSRS...
計劃>50篇

Answer 19

都是课本惹的祸
学了课本上的的标准简洁的SQL
却看不懂专门面向某一数据库的SQL语法
杯具……

Answer 20

为什么hash jion 后在执行上面的 JOIN就正确呢？

Answer 21

我以前都是先存到临时表 ，特别是较多的T-sql逻辑时。

Answer 22

每天学一点SQL知识....时间长了就是积累

Answer 23

学习了!
web 网站建设

Answer 24

学习了!加精
web 网站建设

Answer 25

如果代码没有看错的话，
from 后面 where A_ID=a.A_ID 这个没有返回值，也就是null， 
cast(null as int)当然不会出错。不知道有没有理解对lz的意思。

Answer 26

不懂专门面向某一数据库的SQL语法

Answer 27

SQL2005與SQL2008的引擎區別，生成數據的方式都影響執行計劃 
以下引用MSDN上網友提出的問題，看了一下SQL05與SQL08的順序上有區別 
SQL2005正常，SQL2008出錯 
需要用force order處理學習