你会用微软sql server的Update语句的一个扩展功能,还有如何绕过它的一个bug(首发)吗?

微软Sql server的Update语句，有个不错的扩展功能,通过允许变量赋值,可在语句中嵌入复杂的逻辑计算，从而让本来需要一个Select 加一个Update语句的段子，被优化成只需一个update语句。这对性能提高有不小的好处.但是,这项功能有个尚未报告且不易注意的bug.

举例来说，下述形式都是合法的：

Update table1 set column1 = expr1, @var1 = expr2, @var2= column2 = expr3, ...  where where_condition

 

为了说更透彻的说明问题, 看下面的语句:

UPDATE dbo.TableX
 SET @oldDesc = Description, @OldApr = ApproveBits, 
  @NewApr = case when @OldApr & @txtCheck <> 0 and @oldDesc <> @Description then @OldApr & @txtMask2 
    else @OldApr end, 
  @NewApr = (case when @NewApr & @rateCheck = 0 then @NewApr 
   when @protectlevel >= XRate then @NewApr | @rateBit
   else @NewApr & @rateMask end),
  ApproveBits = @NewApr, --don't as @NewApr=ApproveBits= 
-- parse incorrectly due to a bug
  @dateSet = case when LastModified is null and (@NewApr = 0 or @NewApr = 3) then 1 else 0 end, 
  LastModified = case when @dateSet = 1 then @pnewDate else LastModified end,...
 WHERE ...

 

 

你看了上面的代码会不会晕?. 在Update Clause中, 标准的sql只允许对数据库表的列赋值.由于微软sql的这一扩展, 允许对变量赋值, 再和case 语句组合起来, 就可以实现相当复杂的逻辑计算.如果没有这些,一个Update语句就能做完的事情,就需要一个Select加一个Update语句才能实现.上面的那个例子,全面的利用了这一功能,从而实现对性能的提高和优化.

 

你如果看到上面的代码会晕,那是因为,微软本身提供的文档并没有完整透彻的说明这一功能,经过我个人的测试和总结,补充以下重要条款：

1.由于允许被赋值的是变量，可以在update中嵌入复杂的逻辑计算，并且可以让本来需要一个select 加一个update语句的段子，被优化成只需要一个update语句的段子。这对性能提高有不小的好处。

2。@var2= column2 = expr3, 等同于: @var2= expr3, column2 = expr3

不同于“@var2= column2, column2 = expr3" 或者“column2 = expr3, @var2= column2", 后两种形式都是把column2的旧值赋予了@var2.

3. 形式: column2 = @var2= expr3, 非法.

由于可能出现嵌套引用,所以必须知道在赋值时的优先级,规则如下,

4. 变量赋值相对列赋值优先.

5. 如果都是变量赋值,那么更靠左边的优先.

6. 在赋值表达式右边出现的变量的值是该变量的当前值.

7. 在赋值表达式右边出现的列的值永远是该列的旧值, 与在update语句中的次序无关.

 

目前, 这个扩展功能有一个bug, 就是,在下面的形式中:

"...set @var2 = expr1, @var2= column2 = expr2,... ",

你会想当然地认为@var2最后的值是expr2, 但是,由于这个bug,实际上, var2最后的值是expr1.

为了绕开这个bug, 你可以把上面重写成下面的形式:

@var2 = expr1, @var2= expr2, column2 = @var2

 

这里介绍的都是个人精炼的,在微软的文献中找不到的,值得收藏呵.

weibo:JohnXhark