34,576
社区成员
发帖
与我相关
我的任务
分享
select len(amt),len(amt),* from TestCnt
|--Compute Scalar(DEFINE:([Expr1004]=len(CONVERT_IMPLICIT(varchar(12),[testdb].[dbo].[TestCnt].[amt],0)), [Expr1005]=len(CONVERT_IMPLICIT(varchar(12),[testdb].[dbo].[TestCnt].[amt],0))))
比如这个:
select len(amt)+2,len(amt)+1,* from TestCnt
|--Compute Scalar(DEFINE:([Expr1004]=len(CONVERT_IMPLICIT(varchar(12),[testdb].[dbo].[TestCnt].[amt],0))+(2), [Expr1005]=len(CONVERT_IMPLICIT(varchar(12),[testdb].[dbo].[TestCnt].[amt],0))+(1)))
同样的函数都是在标量计算这一步里面调用的, 从这个结果里分析这么是调用了两次.[/quote]
再进一步, 查询列表中和选择条件中的相同的函数也是分别在不同步骤中执行的, 同样是执行两次.[/quote]
嗯以前我也没注意过这块的东西select len(amt),len(amt),* from TestCnt
|--Compute Scalar(DEFINE:([Expr1004]=len(CONVERT_IMPLICIT(varchar(12),[testdb].[dbo].[TestCnt].[amt],0)), [Expr1005]=len(CONVERT_IMPLICIT(varchar(12),[testdb].[dbo].[TestCnt].[amt],0))))
select len(amt)+2,len(amt)+1,* from TestCnt
|--Compute Scalar(DEFINE:([Expr1004]=len(CONVERT_IMPLICIT(varchar(12),[testdb].[dbo].[TestCnt].[amt],0))+(2), [Expr1005]=len(CONVERT_IMPLICIT(varchar(12),[testdb].[dbo].[TestCnt].[amt],0))+(1)))
select len(amt),len(amt),* from TestCnt
|--Compute Scalar(DEFINE:([Expr1004]=len(CONVERT_IMPLICIT(varchar(12),[testdb].[dbo].[TestCnt].[amt],0)), [Expr1005]=len(CONVERT_IMPLICIT(varchar(12),[testdb].[dbo].[TestCnt].[amt],0))))
select len(amt)+2,len(amt)+1,* from TestCnt
|--Compute Scalar(DEFINE:([Expr1004]=len(CONVERT_IMPLICIT(varchar(12),[testdb].[dbo].[TestCnt].[amt],0))+(2), [Expr1005]=len(CONVERT_IMPLICIT(varchar(12),[testdb].[dbo].[TestCnt].[amt],0))+(1)))
select amt=sum(amt), qtt=sum(qtt), prc=sum(amt)/sum(qtt) from dbo.TestCnt
|--Compute Scalar(DEFINE:([Expr1006]=[Expr1004]/[Expr1005]))
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1007]=(0) THEN NULL ELSE [Expr1008] END, [Expr1005]=CASE WHEN [Expr1009]=(0) THEN NULL ELSE [Expr1010] END))
|--Stream Aggregate(DEFINE:([Expr1007]=COUNT_BIG([testdb].[dbo].[TestCnt].[amt]), [Expr1008]=SUM([testdb].[dbo].[TestCnt].[amt]), [Expr1009]=COUNT_BIG([testdb].[dbo].[TestCnt].[qtt]), [Expr1010]=SUM([testdb].[dbo].[TestCnt].[qtt])))
|--Table Scan(OBJECT:([testdb].[dbo].[TestCnt]))
set statistics profile ON
select amt=sum(amt), qtt=sum(qtt), prc=sum(amt)/sum(qtt) from tbname