|
|
Posted by Tony Rogerson on 10/02/23 11:50
This might help from the SQL Engine team blog...
http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx
Its point 4, the identities are calculated in the right order just not
inserted but the insert order shouldn't matter if the identities are
calculated in the correct order.
1.. If you have an ORDER BY in the top-most SELECT block in a query, the
presentation order of the results honor that ORDER BY request
2.. If you have a TOP in the same SELECT block as an ORDER BY, any TOP
computation is performed with respect to that ORDER BY. For example, if
there is a TOP 5 and ORDER BY clause then SQL Server picks the TOP 5 rows
within a given sort. Note that this does not guarantee that subsequent
operations will somehow retain the sort order of a previous operation. The
query optimizer re-orders operations to find more efficient query plans
3.. Cursors over queries containing ORDER BY in the top-most scope will
navigate in that order
4.. INSERT queries that use SELECT with ORDER BY to populate rows
guarantees how identity values are computed but not the order in which the
rows are inserted
5.. SQL Server 2005 supports a number of new "sequence functions" like
RANK(), ROW_NUMBER() that can be performed in a given order using a OVER
clause with ORDER BY
6.. For backwards compatibility reasons, SQL Server provides support for
assignments of type SELECT @p = @p + 1 ... ORDER BY at the top-most scope.
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Mike C#" <xxx@yyy.com> wrote in message
news:Gylkg.2679$%12.1269@fe09.lga...
>
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
> news:Xns97E41419257FYazorman@127.0.0.1...
>> (bevanward@gmail.com) writes:
>> 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.
>
> I've found that it doesn't work all too often; particularly, as you
> pointed out, if you are running hyperthreading, multiple processors, or
> have multiple programs updating the table simultaneously. In that third
> situation IDENTITY can leave extremely large gaps in a sequence. In my
> experience, the only thing an IDENTITY column can guarantee is a different
> number for each row.
>
> To be honest, I don't think the INSERT statement guarantees the order in
> which the rows will be inserted, which is a large part of the OP's problem
> in this situation. Normally it doesn't matter what order rows get
> inserted as long as they get in there. In this case the OP is dynamically
> assigning numeric identifiers to each row as they're inserted which makes
> the order of insertion important.
>
> BTW - I didn't think about it last night, but with the SELECT INTO
> statement (instead of INSERT) you might be able to use the IDENTITY()
> function to assign values in the order you require. But SELECT INTO
> requires the target table not exist before it's run. I haven't tried it,
> so can't guarantee it would work, but hey...
>
Navigation:
[Reply to this message]
|