今天解答一个帖子时遇到的一个有关问题

今天解答一个帖子时遇到的一个问题。

SQL code

--表T和T2有字段ID和COL, 只是数据类型不同，下面语句想把COL更新为前一条记录的ID，--但是字段为字符型时，结果不是期望的结果，为什么呢？--表TCREATE TABLE T(ID INT, COL INT)INSERT T(ID)SELECT 1 UNION ALLSELECT 2 UNION ALLSELECT 3CREATE CLUSTERED INDEX IDX_ID ON T(ID)DECLARE @COL INT, @COL2 INTUPDATE T SET @COL=@COL2,              @COL2=ID,                             COL=@COLSELECT * FROM TDROP TABLE T/*ID          COL         ----------- ----------- 1           NULL2           13           2*/--表T2CREATE TABLE T2(ID VARCHAR(8), COL VARCHAR(8))INSERT T2(ID)SELECT '00000001' UNION ALLSELECT '00000002' UNION ALLSELECT '00000003'CREATE CLUSTERED INDEX IDX_ID ON T2(ID)DECLARE @COL VARCHAR(8), @COL2 VARCHAR(8)UPDATE T2 SET @COL=@COL2,              @COL2=ID,                             COL=@COLSELECT * FROM T2DROP TABLE T2/*ID       COL      -------- -------- 00000001 NULL00000002 0000000200000003 00000003*/

SQL code

CREATE TABLE T2(ID VARCHAR(8), COL VARCHAR(8))INSERT T2(ID)SELECT '00000001' UNION ALLSELECT '00000002' UNION ALLSELECT '00000003'CREATE CLUSTERED INDEX IDX_ID ON T2(ID)DECLARE @COL INT, @COL2 INTUPDATE T2 SET @COL=@COL2,              @COL2=ID,                             COL=CONVERT(INT,@COL)SELECT * FROM T2DROP TABLE T2（所影响的行数为 3 行）（所影响的行数为 3 行）ID       COL      -------- -------- 00000001 NULL00000002 100000003 2（所影响的行数为 3 行）
[解决办法]
SQL code
---这样也不行啊CREATE TABLE T2(ID VARCHAR(8), COL VARCHAR(8))INSERT T2(ID)SELECT '00000001' UNION ALLSELECT '00000002' UNION ALLSELECT '00000003'CREATE CLUSTERED INDEX IDX_ID ON T2(ID)DECLARE @COL VARCHAR(8), @COL2 VARCHAR(8),@col3 varchar(8)UPDATE T2 SET @col3=isnull(0,null),              @col=@col3,              @COL3=@COL2,              @COL2=ID,              COL=@COL3SELECT * FROM T2DROP TABLE T2/*ID       COL-------- --------00000001 NULL00000002 0000000200000003 00000003(3 行受影响)*/
[解决办法]
DECLARE @COL VARCHAR(8), @COL2 VARCHAR(8),@COL3 VARCHAR(8), @COL4 VARCHAR(8)
UPDATE T2 SET @COL=@COL2,
             @col3=@col2,
             @COL2=ID,
             @col4=@col2,
             col=@col
select @col,@col3,@COL4

结果：

000000030000000300000003

declare @col int,@col2 int,@col3 int,@col4 int
UPDATE T2 SET @COL=@COL2,
             @col3=@col2,
             @COL2=ID,
             @col4=@col2,
             COL=@COL
select @col,@col3,@COL4

结果：

223

不解中＝。＝！
[解决办法]
DECLARE @COL INT, @COL2 INT
UPDATE T SET @COL=@COL2, @COL2=ID,COL=@COL

也没什么不好理解的。这种写法本来就很混乱，没人会推荐这样写的。

@col 和 @col2  本身的变化倒是会和你的期望一样顺序变化，但涉及到更新字段col时数据库本身有一些说不清楚的自动优化，自然会有一些说不准的结果。
  @COL=@COL2, @COL2=ID,COL=@COL 这个说不定就推断成了 col=id 的恒等变换了

用来作字符累加等还是不错的

[解决办法]
看执行计划的区别，varchar的有点乱了，多了个中间表达式

int
1-[@aCOL2]=id
2-[@aCOL] = [@aCOL2]  
3-[dbo].[T2].[COL] = [@aCOL]

varchar
1-[@aCOL2]=id
2-[@aCOL] = [@aCOL2]
3-[Expr]=[@COL]
4-[T2].[COL] = [Expr1004]

[解决办法]
猜了一下，
字符串的情况下，SQL自动优化时执行顺序可能变成了如下情况：
(1)@COL2=ID
(2)@COL=@COL2 
 
(3)COL=@COL

好像只有这样才解释得通。

ps：纯属臆测，由此导致任何后果均不负责
[解决办法]
CREATE TABLE T2(ID VARCHAR(8), COL VARCHAR(8))
INSERT T2(ID)
SELECT '00000001' UNION ALL
SELECT '00000002' UNION ALL
SELECT '00000003'

CREATE CLUSTERED INDEX IDX_ID ON T2(ID)

---#####################################--
--楼主的语句 更新语句  
DECLARE @COL VARCHAR(8), @COL2 VARCHAR(8)
UPDATE T2  
SET  
@COL=@COL2,
@COL2=ID,  
       COL=@COL

--结果
/*
ID       COL     
-------- --------  
00000001 NULL
00000002 00000002
00000003 00000003

（所影响的行数为 3 行）
*/

--对应的执行计划
/*StmtText  
StmtText                                                                                                                         
---------------------------------------------------------  
 |--Clustered Index Update(OBJECT:([Test].[dbo].[T2].[IDX_ID]), SET:([@COL]=[Expr1004], [@COL2]=[Expr1005], [T2].[COL]=[@COL]))
      |--Compute Scalar(DEFINE:([Expr1004]=[@COL]=[@COL2], [Expr1005]=[@COL2]=[T2].[ID]))
           |--Top(ROWCOUNT est 0)
                |--Clustered Index Scan(OBJECT:([Test].[dbo].[T2].[IDX_ID]))

*/
---#####################################--

--修改(改了变量大小)
DECLARE @COL VARCHAR(9), @COL2 VARCHAR(8)
UPDATE T2  
SET  
@COL=@COL2,
@COL2=ID,  
       COL=@COL


--结果
/*
ID       COL     
-------- --------  
00000001 NULL
00000002 00000001
00000003 00000002

（所影响的行数为 3 行）
*/

--对应的执行计划
/*
--StmtText                                                                                                                             
-------------------------------------------------------------  
 |--Clustered Index Update(OBJECT:([Test].[dbo].[T2].[IDX_ID]), SET:([@COL]=[Expr1004], [@COL2]=[Expr1005], [T2].[COL]=[Expr1006]))
      |--Compute Scalar(DEFINE:([Expr1004]=[@COL]=Convert([@COL2]), [Expr1005]=[@COL2]=[T2].[ID], [Expr1006]=Convert([@COL])))
           |--Top(ROWCOUNT est 0)
                |--Clustered Index Scan(OBJECT:([Test].[dbo].[T2].[IDX_ID]))

*/