tsql - Are the 'inserted' and 'deleted' tables guaranteed to return their records in the same order in an AFTER UPDATE trigger? -

if have after update trigger, will

select * inserted 

and

select * deleted 

give me records in same order?

i.e. lets able index result sets, del[5] , ins[5] give me matching entries, if 1 value of compound primary key has changed (which reason why inner join won't work).

i don't believe there any guarantee on ordering of rows within inserted , deleted - there no ordering guarantee selecting table, without specifying order by.

---

i decided see if produce script demonstrate lack of ordering. on machine (sql 2008 dev), can repeatably run following script. outputs 2 rows inserted , deleted. note don't touch id column, , if supposition correct (that ordered in way), same ids should appear in same order. not case here.

first, outputs:

id          d1                      v1 ----------- ----------------------- ---------------------------------------------------------------------------------------------------- 32          2010-03-01 00:00:00.000 text 60          2010-02-01 00:00:00.000 text  (2 row(s) affected)  id          d1                      v1 ----------- ----------------------- ---------------------------------------------------------------------------------------------------- 60          2010-03-01 00:00:00.000 text 32          2010-02-01 00:00:00.000 text  (2 row(s) affected) 

and script produced this:

create table t1 (     id int not null,     d1 datetime not null,     v1 varchar(100) not null,     constraint pk_t1 primary key (d1,id) ) go create index ix_t1_d1 on t1(d1) go insert t1(id,d1,v1) select id,dateadd(day,id-1,'20100101'),'text' (select row_number() on (order so1.id) sysobjects so1,sysobjects so2,sysobjects) t(id) go create trigger t_t1_u on t1 after update begin     select * inserted     select * deleted end go sp_configure 'disallow results triggers',0 go reconfigure go update t1 set d1 = dateadd(month,case when datepart(month,d1)=2 1 else -1 end,d1) d1 in ('20100201','20100301') go sp_configure 'disallow results triggers',1 go reconfigure go drop table t1 go