插入固定表后排序变化问题：最后一位跑到第一位去了

Question

/*
今天遇到一个奇怪的问题：
直接查询时115号排第一位,103号排最后一位，可插入到固定表AA时，最后一位103却跑到第一位去了。
是什么原因啊？
详情如下：
*/--1、本身就有的固定表AA
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AA]
GOCREATE TABLE [dbo].[AA] (
[s_no] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[s_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[sex] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[V_NameType] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[V_NameState] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[V_NameShift] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL 
) ON [PRIMARY]
GO--2、直接查询时，115号排第一位，103号排最后一位，是正常的
Select B.s_no,B.s_name,B.sex,B.V_NameType,B.V_NameState,B.V_NameShift
--,ID=Identity(int,1,1) Into AA  
From SN_JiShi_PaiWei As A left outer join set_jishi as B on B.s_no=A.ID_JiShi 
where A.State_JS='K' and A.AllType='1'  
Order By A.No_PaiWei Asc
--3、先drop table AA
drop table AA
--4、插入到固定表AA就出问题了:115号排第二位，103号排第一位了。什么原因啊？
Select B.s_no,B.s_name,B.sex,B.V_NameType,B.V_NameState,B.V_NameShift
,Identity(int,1,1) as ID  Into AA
From SN_JiShi_PaiWei As A left outer join set_jishi as B on B.s_no=A.ID_JiShi 
where A.State_JS='K' and A.AllType='1' 
Order By A.No_PaiWei  
--5、查询定表AA
select * from AA order by id/*查询结果：
直接查询时的结果：正确115 肖静静 女 足浴技工 空闲 足浴早班-A
119 肖红花 女 足浴技工 空闲 足浴早班-A
103 孙单 女 足浴技工 空闲 足浴早班-A查询定表AA的结果：错误
103 孙单 女 足浴技工 空闲 足浴早班-A 1
115 肖静静 女 足浴技工 空闲 足浴早班-A 2
119 肖红花 女 足浴技工 空闲 足浴早班-A 3
*/

Answer 1

--4、插入到固定表AA就出问题了:115号排第二位，103号排第一位了。什么原因啊因为有个IDENTITY列,就是因为它,

Answer 2

TO :SQL77
按道理115号的ID应该为1才对啊。难道IDENTITY的顺序不是取决于最后面的order by 吗？

Answer 3

Select B.s_no,B.s_name,B.sex,B.V_NameType,B.V_NameState,B.V_NameShift 
,Identity(int,1,1) as ID  Into AA 
From SN_JiShi_PaiWei As A left outer join set_jishi as B on B.s_no=A.ID_JiShi 
where A.State_JS='K' and A.AllType='1' 
Order By A.No_PaiWei  
这句按照Order By A.No_PaiWei排序插入的。

Answer 4

以前经常碰到,汗,一下子要弄出来那个测试还想不到,有时候ORDER BY 没用

Answer 5

http://topic.csdn.net/t/20030212/18/1422194.html
参考一下这个,不过我以前弄的时候也有碰到过,ORDER BY 不起作用,

Answer 6

create   table   #t1(f1   varchar(10),f2   int)   
    
  insert   into   #t1   select   'A01',   11   
  insert   into   #t1   select   'A01',   10     
  insert   into   #t1   select   'B01',   15   
  insert   into   #t1   select   'B01',   16   
  insert   into   #t1   select   'C01',   18       
  insert   into   #t1   select   'C01',   19       
  insert   into   #t1   select   'C01',   17     select   identity(int,1,1)   as   F1,F2     
      into   #temp1   
      from   #t1   
    order   by  F1, f2      select   identity(int,1,1)   as   F3,F2     
      into   #temp2   
      from   #t1   
    order   by   F1,f2   
SELECT * FROM #T1 ORDER BY F1,F2      
SELECT * FROM #temp1 ORDER BY F1,F2
SELECT * FROM #temp2 ORDER BY F3,F2
DROP TABLE #temp1,#temP2,#T1
 /*
f1         f2          
---------- ----------- 
A01        10
A01        11
B01        15
B01        16
C01        17
C01        18
C01        19（所影响的行数为 7 行）F1          F2          
----------- ----------- 
1           11
2           10
3           15
4           16
5           18
6           19
7           17（所影响的行数为 7 行）F3          F2          
----------- ----------- 
1           10
2           11
3           15
4           16
5           17
6           18
7           19（所影响的行数为 7 行）*/
看一下,呵呵,这样的效果

Answer 7

这个要看into的时候做了哪些操作了。问微软

Answer 8

很奇怪的問題，我根據9樓的例子試驗了下，並沒有出現亂序問題
create   table   #t1(f1   varchar(10),f2   int) 
    
  insert   into   #t1   select   'A03',   11   
  insert   into   #t1   select   'A01',   10     
  insert   into   #t1   select   'B01',   15   
  insert   into   #t1   select   'B01',   16   
  insert   into   #t1   select   'C01',   18       
  insert   into   #t1   select   'C01',   19       
  insert   into   #t1   select   'C01',   17     select   identity(int,1,1)   as   a1,F2     
      into   #temp1   
      from   #t1   
    order   by   f1,f2    select * from #temp1a1          F2
----------- -----------
1           10
2           11
3           15
4           16
5           17
6           18
7           19

Answer 9

而且根據執行計劃上是原表掃描=〉排序=〉獲取前幾行資料=〉運算identity=>資料表插入=>select into樓主能發出下你select into時候的執行計劃嗎？

Answer 10

这个问题比较难说清楚只能说是因为你的 A.No_PaiWei  的值不是唯一的，排序时造成了这样的误差。要解决这个问题，你可以改成Select B.s_no,B.s_name,B.sex,B.V_NameType,B.V_NameState,B.V_NameShift
,Identity(int,1,1) as ID  Into AA
From SN_JiShi_PaiWei As A left outer join set_jishi as B on B.s_no=A.ID_JiShi
where A.State_JS='K' and A.AllType='1'
Order By A.No_PaiWei  ,B.s_no

Answer 11

77哥別激動
你看我這裡運行結果f1         f2
---------- -----------
A01        10
A01        11
B01        15
B01        16
C01        17
C01        18
C01        19(7 個資料列受到影響)F1          F2
----------- -----------
1           10
2           11
3           15
4           16
5           17
6           18
7           19(7 個資料列受到影響)F3          F2
----------- -----------
1           10
2           11
3           15
4           16
5           17
6           18
7           19(7 個資料列受到影響)
完全考慮你的SQL阿，，，沒有任何改動，，
但是第2個結果是不一樣啊？

Answer 12

呵呵,我没激动啊,不会吧,莫非真的与版本有关系?
我帖的上面的那个帖子里面就说版本可能有关系的,
我测试第二个不一样的,
因为IDENTIT的别名与排序前的那个名字相同为F1

Answer 13

額，，，原來如此，，，
下次記得發SQL版本號了，，，
這個頗Into還區分2005/2000的不同啊，，，
是2005有先進行排序，再into呢

Answer 14

create   table   #t1(f1   varchar(10),f2   int)   
    
  insert   into   #t1   select   'A01',   11   
  insert   into   #t1   select   'A01',   10     
  insert   into   #t1   select   'B01',   15   
  insert   into   #t1   select   'B01',   16   
  insert   into   #t1   select   'C01',   18       
  insert   into   #t1   select   'C01',   19       
  insert   into   #t1   select   'C01',   17     select   identity(int,1,1)   as   F1,F2     
      into   #temp1   
      from   #t1   
    order   by  F1, f2      select   identity(int,1,1)   as   F3,F2     
      into   #temp2   
      from   #t1   
    order   by   F1,f2  
 
SET SHOWPLAN_TEXT ON
StmtText                       
------------------------------ 
SET STATISTICS PROFILE ON（所影响的行数为 1 行）StmtText                                                                                                                    
--------------------------------------------------------------------------------------------------------------------------- 
select   identity(int,1,1)   as   F1,F2     
      into   #temp1   
      from   #t1   
    order   by  F1, f2（所影响的行数为 1 行）StmtText                                                                                                                                                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  |--Table Insert(OBJECT:([#temp1]), SET:([#temp1].[F2]=[#t1].[f2], [#temp1].[F1]=[Expr1003]))
       |--Top(ROWCOUNT est 0)
            |--Compute Scalar(DEFINE:([Expr1003]=setidentity([Expr1002], -7, 0, '#temp1')))
                 |--Sort(ORDER BY:([Expr1002] ASC, [#t1].[f2] ASC))
                      |--Compute Scalar(DEFINE:([Expr1002]=getidentity(-7, 0, '#temp1')))
                           |--Table Scan(OBJECT:([tempdb].[dbo].[#t1_________________________________________________________________________________________________________________000000000020]))（所影响的行数为 6 行）StmtText                                                                                                                
----------------------------------------------------------------------------------------------------------------------- 
 select   identity(int,1,1)   as   F3,F2     
      into   #temp2   
      from   #t1   
    order   by   F1,f2（所影响的行数为 1 行）StmtText                                                                                                                                                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  |--Table Insert(OBJECT:([#temp2]), SET:([#temp2].[F2]=[#t1].[f2], [#temp2].[F3]=[Expr1003]))
       |--Top(ROWCOUNT est 0)
            |--Compute Scalar(DEFINE:([Expr1003]=setidentity([Expr1002], -7, 0, '#temp2')))
                 |--Compute Scalar(DEFINE:([Expr1002]=getidentity(-7, 0, '#temp2')))
                      |--Sort(ORDER BY:([#t1].[f1] ASC, [#t1].[f2] ASC))
                           |--Table Scan(OBJECT:([tempdb].[dbo].[#t1_________________________________________________________________________________________________________________000000000020]))（所影响的行数为 6 行）StmtText                      
----------------------------- 
SET STATISTICS PROFILE OFF（所影响的行数为 1 行）2000的执行计划,第一个是先计算,再排序,第二个是排序,再计算

Answer 15

ORDER BY 就是处理这种排序问题的

Answer 16

问题已解（用的其他方法）。但是真正原因还是没弄清楚。实际情况是：ID_JiShi 为唯一的聚簇索引时就会出现如题错误；若将No_PaiWei 改为唯一的聚簇索引时，就正常。为什么会这样呢？

Answer 17

当某列设置为聚集索引时，表中的数据存放物理顺序是按照聚集索引的顺序来存放的。。且默认为升序排列
如果你的聚集索引为ID，当然插入数据后，在表中数据行是以ID为升序排列的。
如果为No_PaiWei。自然就是按照No_ParWei排序了，所以就正常了。

Answer 18

在与 SELECT...INTO 语句一起使用以从另一来源插入行时，ORDER BY 子句不能保证按指定的顺序插入这些行。

Answer 19

如果2005 那就用row_number函数

Answer 20

05的话，SQLSERVER也不保证，
用row_number吧

Answer 21

我认为不是order by 不能保证按指定的顺序插入这些行。 
原因是因为
insert   into   #t1   select   'A01',   11   
insert   into   #t1   select   'A01',   10     
列f1 与 f2 中 f1的值相同都是 A01, 而f2中10的值小于11 ，故第二行数据排在了第一位。

Answer 22

http://blog.csdn.net/misterliwei/archive/2010/01/05/5134043.aspx
http://support.microsoft.com/?scid=kb%3Ben-us%3B273586&x=2&y=14

Answer 23

有道理，另外，闲云野鹤说的也对，今天才看到技术内幕丛书里的SQL2008 T-SQL基础里好像讲到ORDER BY失效的情况。还没看到这块。

Answer 24

最好不要用identity自动增长列

Answer 25

order by 不是万能的..
Select B.s_no,B.s_name,B.sex,B.V_NameType,B.V_NameState,B.V_NameShift 
,Identity(int,1,1) as ID  Into AA 
From SN_JiShi_PaiWei As A left outer join set_jishi as B on B.s_no=A.ID_JiShi 
where A.State_JS='K' and A.AllType='1' 
Order By A.No_PaiWei 
这里的order by其实并不能保证你要的排序 微软官方文档没有明确说明这点所以不推荐大家在这个时候用order by 来确保你要的排序所以在2005 推出的row_number()可以很好解决问题