sql 2008 cursor difference with sql2000 ???

Question

there is a difference between how the following code runs on sql2008 and sql2000. in sql 2000 the result is correct( the fetch is normal from the first row to the last row) while in sql 2008 the fetch is showing strange behavior( starts from the last inserted row till the first one. below is the code of the problem where 'area' is any table:

create trigger tr on area for insert as
    declare @id int
    select @id = id from inserted
    print 'trigger: ' + convert(varchar(50), @id)

    declare c cursor scroll for select id from inserted order by id
open c
fetch next from c into @id
while @@FETCH_STATUS = 0
begin
    print 'cursor id: ' + convert(varchar(50), @id)
    fetch next from c into @id
end
close c
deallocate c

below is the result showig in sql 2008 :

trigger: 1828
cursor id: 1837
cursor id: 1836
cursor id: 1835
cursor id: 1834
cursor id: 1833
cursor id: 1832
cursor id: 1831
cursor id: 1830
cursor id: 1829
cursor id: 1828

and the result showing in sql 2000 is :

trigger: 1837
cursor id: 1828
cursor id: 1829
cursor id: 1830
cursor id: 1831
cursor id: 1832
cursor id: 1833
cursor id: 1834
cursor id: 1835
cursor id: 1836
cursor id: 1837

Answer 1

If you use cursor in SQL2008, it gets from last to first. You are right, SQL changed its selection rule. In general it is better to use cursor like this. Use OrderBy.

Answer 2

You don't specify an ORDER BY clause in the select statement which defines you cursor. Without ORDER BY, the order of rows in a result set is non-deterministic.

This is the case in both SQL 2000 and 2008 - it's essentially a coincidence that the row orders are the way they are.

EDIT

I created a test on SQL 2008 SP1, including the ORDER BY clause added by the OP on first edit, and cannot replicate the described behaviour:

create table area
(id int)
GO
create trigger tr on area for insert as
    declare @id int
    select @id = id from inserted
    print 'trigger: ' + convert(varchar(50), @id)

    declare c cursor scroll for select id from inserted order by id
open c
fetch next from c into @id
while @@FETCH_STATUS = 0
begin
    print 'cursor id: ' + convert(varchar(50), @id)
    fetch next from c into @id
end
close c
deallocate c
GO

insert area
select 10
union select 9
union select 8
union select 7
union select 6
union select 5
union select 4
union select 3
union select 2
union select 1

I got the following result

trigger: 1
cursor id: 1
cursor id: 2
cursor id: 3
cursor id: 4
cursor id: 5
cursor id: 6
cursor id: 7
cursor id: 8
cursor id: 9
cursor id: 10