旧帖釋疑(Order by NewID())

Question

SQL1执行计划 & IO 資料表 'tb'。掃描次數 13，邏輯讀入 13，實體讀取 0，先讀讀入 0。Rows        Executes    StmtText                                                                               
----------- ----------- --------------------------------------------------------------------------------------
4           1             |--Nested Loops(Left Semi Join, OUTER REFERENCES:([a].[aa], [a].[bb]))              
12          1                  |--Table Scan(OBJECT:([tempdb].[dbo].[tb] AS [a]))                             
4           12                 |--Row Count Spool                                                             
4           12                      |--Filter(WHERE:([a].[bb]=[tb].[bb]))                                     
12          12                           |--Sort(TOP 1, ORDER BY:([Expr1003] ASC))                            
48          12                                |--Compute Scalar(DEFINE:([Expr1003]=newid()))                  
48          12                                     |--Table Scan(OBJECT:([tempdb].[dbo].[tb]), WHERE:([tb].[aa]=[a].[aa]))

Answer 1

SQL2执行计划 & IO.資料表 'tb'。掃描次數 4，邏輯讀入 4，實體讀取 0，先讀讀入 0。
Rows        Executes    StmtText                                                                              
----------- ----------- --------------------------------------------------------------------------------------
3           1             |--Filter(WHERE:([a].[bb]=[tb].[bb]))                                               
12          1                  |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[aa]))                       
12          1                       |--Table Scan(OBJECT:([tempdb].[dbo].[tb] AS [a]))                        
12          12                      |--Hash Match(Cache, HASH:([a].[aa]), RESIDUAL:([a].[aa]=[a].[aa]))       
3           3                            |--Sort(TOP 1, ORDER BY:([Expr1004] ASC))                            
12          3                                 |--Compute Scalar(DEFINE:([tb].[bb]=[tb].[bb], [Expr1004]=newid()))
12          3                                      |--Table Scan(OBJECT:([tempdb].[dbo].[tb]), WHERE:([tb].[aa]=[a].[aa])) 

Answer 2

如果不看执行计划，你会认为它们结果会不一样吗？SQL1跟SQL2的区别仅仅是将 IN 改成 =　而已.另外，别误会，我不是来解释啥的，我所知道的也只是一点皮毛而已.我是希望有人来解释一下SQL1跟SQL2之间为什么会有不同的执行计划.

Answer 3

没太明白楼主的意思,你的意思是不是如果没有NEWID(),肯定结果是一样,为什么有了NEWID,结果就可能会不一样?
我也不是来解释,我喜欢这样的讨论.
稍后我也会贴个有趣的现象.

Answer 4

我知道看过执行计划就很容易知道这两段ｓｑｌ之间为什么会造成不一样的结果但我是在想，为什么仅仅将SQL1 的 IN 改成 = 后，执行计划就变了呢？

Answer 5

-----
不认为查询计划不同是造成结果不同的原因。
结果不同是因为newid是变化的查询计划不同是因为in和=

Answer 6

楼主可以把order by newid()去掉，再看一下查询计划

Answer 7

我所指的结果不同，是指SQL1的返回的行数是不固定的，而SQL2返回的行数是固定的３行.

Answer 8

[引用 9 楼 wangdehao 的回复:]不认为查询计划不同是造成结果不同的原因。
结果不同是因为newid是变化的查询计划不同是因为in和=[/Quote]对...是 IN 跟 = 

Answer 9

......,加不加NEWID,执行计划都是那样啊,但是结果为什么不同,也因为是NEWID起的效果,至于IN和=的执行计划为什么不同
这个应该是微软来解释吧,而且如果你认为是等价的,那么就没有存在的必要了啊,实际上处理子表有NULL的情况下,IN和=就会返回
不同的结果,而且如果子查询里没有TOP语句,返回多个结果集,使用=还会错误.
所以,还是不明白楼主想考虑什么,呵呵.

Answer 10

通俗的说，结果不同是因为每次生成的newid()都是不同的，即使你sql1和sql2完全一致，结果也是不同的 查询计划只是到达同一结果的不同途径而已

Answer 11

这个问题搂主应该先执行下这两个语句，你会发现，第一个语句可能返回多条记录，也可能两条
而第二个语句肯定三条记录这会不会是SQL的bug?

Answer 12

楼主的目的就是让人给解释下sql server的思维!
楼主当然知道查询计划不同了!

Answer 13

SQLSERVER内部执行顺序和原理,在T-SQL Querying一书里有说明,大家可以去看下.

Answer 14

那本书我看了好几遍了...书本上的知识，不足以解釋得清楚这个问题.各位可以想一下
SQL1跟SQL2的逻辑上意思应是等价的吧,
但得出来的结果为什么会不一样? 
(再重申一次，我指的不一样是返回的行数不一致)

Answer 15

晕菜了...
讨论了这么久，你都没有执行过我的SQL?
执行几次你就知道我所指的问题了。

Answer 16

从计划中可以，SQL1的子查询执行了12次,应该分别是aa=1,aa=1,aa=1,aa=1,aa=2,aa=2,aa=2,aa=2,aa=2,aa=3,aa=3,aa=3,aa=3SQL2的子查询执行了3次，应该分别是aa=1,aa=2,aa=3，那么，其它9条记录的数从哪来呢？Hash Match
所以SQL1的得到的记录数是不固定的，可能记录为0，也可能记录为12，因为有时它可能得不到匹配的记录
但SQL2每次都是三条记录，因为子查询执行的结果会用四次，也就是说，总有一条记录是匹配的。

Answer 17

呵呵，楼上说的好.
执行计划的解读是这样，如你所说，而且我开始也打开了SET STATISCTIS IO 验证没错.我的不解的地方:将 IN 改成 = , SQL Server会认为我的逻辑不一样了吗?我的子查询里都搭配上了 TOP 1 的哦.

Answer 18

IN和=对于SQL来说是不同的运算符呀,每种运算符都有它自己的运算方法和规则,
就像3+2=5 , 3-2=1至于TOP 1,对于IN来说,这并不影响它的算法;而对于=来说,这是必然的,否则就出错了

Answer 19

看来sql server在 in 和 = 两个方面就是采用不同的方式处理的。
我试了下aa为主键，或是不为主键但为聚集
结果发现就算是查询的结果相同，查询的方式也是不同的。

Answer 20

那倒也是，IN 跟 = 是不同的运算，
可是抛开执行计划不看，你会认为SQL1跟SQL2会返回不一样的行数吗？

Answer 21

估计是SQL Server的解析问题,
IN 解析为 Left semi Join,
= 解析为Inner Join.
所以匹配就不一致了,

Answer 22

一般我们写这样的语句，都是aa值是唯一的，所以一般就认为两种写法是相同的。
我们如果单看这个查询：SELECT * FROM tb a
WHERE  bb in 
    (
    SELECT TOP 1 bb FROM tb
    WHERE aa=a.aa
    ORDER BY NEWID()
    ) 
其实想想，它本来就应该有两种表达意思。但对于sql server来讲，只能取一种，另一种就只能用其它的办法，如=更能表达这个意思了。

Answer 23

如果aa的值是唯一的话,那子查询里的这个TOP 1 又是多余的咯?

Answer 24

我也有点疑惑了，本希望分解执行计划每一步，看看它实际上是怎么关联计算得出的结果集
结果，才研究第一个图表“TABLE SCAN”，发现预计的执行计划是一样的，但是在实际的执行计划中的，实际返回的记录数
第一种写法返回的是48行，而第二种返回的确是12行，里面的输出，筛选等都是一样，为什么实际执行的时候却不一样，理解上
第一次操作的时候：第一种写法，是做了这样的运算：
SELECT * FROM T1 A
INNER JOIN T2 B
   ON A.ID = B.ID  --返回48行第二种我不知道它是怎么做的，返回的12行关键是，显示出来给我们看的参数一样，条件一样，实际返回却不一样，疑惑！

Answer 25

http://topic.csdn.net/u/20070516/15/44cd376c-bab9-47c8-a522-c577199d80e7.html
我的帖子中也有类似疑问

Answer 26

IN 后面需要一个标量集或者一个单列行集
= 后面需要一个标量值所以同一条SQL语句会不同对待

Answer 27

如果把SQL1跟SQL2的 ORDER BY NEWID() 去掉，你能解釋一下为什么返回的行数又相同了呢?:-)

Answer 28

可不可以这么理解：in：永远都当后面的语句返回的是一个结果集，而且是未知的.
在这里当是使用了order by newid() 的时候,当历遍每一行的时候去in的时候得到的结果不一致所致.
这就是为什么会产生不同记录的原因？=：个人认为就确定了值.

Answer 29

如果是这样解释的话,
这个又理解不通,--2005
SET SHOWPLAN_TEXT ON
GO
SET STATISTICS IO ON
GO
SELECT * FROM tb a WHERE  bb IN ('1')
StmtText
---------------------------------------------------------------------------------------------------------------------------------------
  |--Table Scan(OBJECT:([CSDN].[dbo].[tb] AS [a]), WHERE:([CSDN].[dbo].[tb].[bb] as [a].[bb]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)))
--SQL2 
SELECT * FROM tb a WHERE  bb = ('2') 
StmtText
---------------------------------------------------------------------------------------------------------------------------------------
  |--Table Scan(OBJECT:([CSDN].[dbo].[tb] AS [a]), WHERE:([CSDN].[dbo].[tb].[bb] as [a].[bb]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0))) 
莫非是2005里面对IN后面之有一个值的情况做过优化?

Answer 30

是这样,就是NEWID()导致的执行行为有变化,请看我的回复,你只要能解释那个,就能解释这个问题
去掉NEWID以后,SQLSERVER第一步做的事情,实际返回的行数就一样了,而有NEWID,第一步操作上
虽然看上去是完全一摸一样,实际上内部返回的用做下一步输入的行就已经完全不相等了.

Answer 31

如果是这样解释的话,
这个又理解不通,SQL code    --2005 SET SHOWPLAN_TEXT ON GO SET STATISTICS IO ON GO SELECT * FROM tb a WHERE bb IN ('1') StmtText --------------------------------------------------------------------------------------------------------------------------------------- |--Table Scan(OBJECT:([CSDN].[dbo].[tb] AS [a]), WHERE:([CSDN].[dbo].[tb].[bb] as [a].[bb]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0))) --SQL2 SELECT * FROM tb a WHERE bb = ('2') StmtText --------------------------------------------------------------------------------------------------------------------------------------- |--Table Scan(OBJECT:([CSDN].[dbo].[tb] AS [a]), WHERE:([CSDN].[dbo].[tb].[bb] as [a].[bb]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)))
莫非是2005里面对IN后面之有一个值的情况做过优化?
----------
个人理解：in 会根据后面结果集的不同而做不同的执行计划.

Answer 32

要不用 NEWSEQUENTIALID() 代替NEWID() 试试看

Answer 33

加不加 ORDER BY NEWID() 导致的问题, 我想主要是跟 NEWID() 这个函数有关在 sql server 中, 除了 NEWID 这个函数外, 其他函数不管是不是确定性函数, 都不会出现同一查询中出来不同值的问题例如, 虽然 GETDATE() 是返回当前时间, 但如果你在一个耗时1分钟的 SELECT 中添加一列, 其值为函数 GETDATE() , 你会发现这个查询的结果中, 这个添加的列值是一样的, 并不会因为你查询运行了 1 分钟, 从而在不同的记录中产生不同的 GETDATE() 值
但 NEWID() 就不一样了, 不管你怎么调用, 两个 NEWID() 的值就不会一样
应该正是因为 NEWID() 这种特殊性, 所以 sql server 在处理查询有时候, 有特别的处理, 故导致了加 ORDER BY NEWID() 之后的那种效果
大家可以试试 ORDER BY 其他, 其效果和不加ORDER BY 都是一样的