IDENTITY with inline sql works different in 2005 than 2000

Hi All:
In SQL 2000, I used this SQL statement to assign an ID to a sorted recordset:
SELECT VPSystemID, ID,IDENTITY(int, 1, 1) AS SO INTO
arqdouj_20Aug07_104036_3843636
FROM (
SELECT TOP 100 PERCENT VPSystemID,ID FROM VPAPointUser_marqdouj ORDER BY
[VPM Distance],Joint,IndicationTypeID,Active DESC
) Data
The resulting inline recordset was sorted *BEFORE* the identity was applied.
It appears to me now, that in 2005 the identity is applied before the inline
sql recordset is sorted; at least that is the results I am seeing.
Should I expect the recordset to be sorted first? Is this a bug with 2005?
Is there some other method I should be using to achieve the results I want?
Thanks.
--
Doug.> Should I expect the recordset to be sorted first? Is this a bug with
> 2005?
In the opinion of a lot of people, the (undocumented) behavior in SQL Server
2000 was the bug. A lot of people complain that 2005 "broke" their code,
but in all honesty, they shouldn't have been relying on undocumented
behavior. Usually the example is
CREATE VIEW dbo.foo
AS
SELECT TOP 100 PERCENT ...
ORDER BY bar;
GO
SELECT * FROM dbo.foo; -- no ORDER BY!
> Is there some other method I should be using to achieve the results I
> want?
Well, you can generate the rank using ROW_NUMBER() OVER (ORDER BY ...)
however that does not make an IDENTITY column if that is what you're
expecting.
A|||Hi Aaron:
Thanks for the reply.
I did eventually find the row_number() function, and was able to get the
results I wanted. I didn't need an identity column, just a field that
contains the sort order.
Doug.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Should I expect the recordset to be sorted first? Is this a bug with
> > 2005?
> In the opinion of a lot of people, the (undocumented) behavior in SQL Server
> 2000 was the bug. A lot of people complain that 2005 "broke" their code,
> but in all honesty, they shouldn't have been relying on undocumented
> behavior. Usually the example is
> CREATE VIEW dbo.foo
> AS
> SELECT TOP 100 PERCENT ...
> ORDER BY bar;
> GO
> SELECT * FROM dbo.foo; -- no ORDER BY!
> > Is there some other method I should be using to achieve the results I
> > want?
> Well, you can generate the rank using ROW_NUMBER() OVER (ORDER BY ...)
> however that does not make an IDENTITY column if that is what you're
> expecting.
> A
>
>