Posted by Erland Sommarskog on 10/02/74 11:44

Doug (drmiller100@hotmail.com) writes:
> if you can use a stored procedure instead of a view, you could select
> the data INTO a temp table in the "correct order", alter the table to
> add an identity column, and return that ordered by identity.
> before someone gets excited, there isn't a GUARANTEE this will work
> forever in future versions of SQL, but it probably will.

There is no guarantee that it will work any version of SQL Server. In fact
for a result set of any size, I would not expect it to work.

What is guaranteed to work, at least in SQL 2005, is if you have a
table with an IDENTITY table, and perform an INSERT with an ORDER BY.

Note that this does not apply to SELECT INTO with the IDENTITY function
and ORDER BY. In that case, there is *no* guarantee.

--
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

[Back to original message]