|
|
Posted by Erland Sommarskog on 10/02/44 11:50
(bevanward@gmail.com) writes:
> I am finding unexpected results when inserted into a newly created
> table that has a field of datatype int identity (1,1).
>
> Basically the order I sort on when inserting into the table is not
> reflected in the order of the values from the identity field.
>
> Have I been wrong in assuming that it should reflect the order from the
> sort?
>
> The code is ...
>
> create table tmp (A varchar(50), L float, C int identity(1,1))
> insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
>
> and I don't understand why the values in tmp.C aren't in the order
> suggested by the sort.
Interesting. I get it to work most of the time, and I've even been told
that this is guarranteed to work as expected. Definitely in SQL 2005,
but the source said it was OK for SQL 2000 as well.
However, if you are running on a multi-processor machine (including a
hyper-threaded CPU), try adding OPTION (MAXDOP 1) at the end of the
query.
Note that is you use SELECT INTO instead, there is no guarantee that
the order is the desired.
By the way, what does SELECT @@version say?
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|