Consider the following example:

Create Table Test ([id] int, [value] varchar(max)) Go Create Table Test_Log ([row_id] int, [action] varchar(11), [timestamp] DateTime default GetDate(), [id] int, [value] varchar(max)) Go Create Trigger Tr_Update_Test on Test after Update AS Begin insert into Test_Log([row_id], [action], [id], [value]) select * from( (Select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [row_id], 'UPDATEFROM' as [action], [id], [value] From deleted) UNION (Select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [row_id], 'UPDATETO' as [action], [id], [value] From inserted) )AS [temp_table] ORDER BY [temp_table].[row_id], [temp_table].[action] End Go 

I want to add row numbers to the log table to match the before and after updates. I don't want an expensive sort, I just want to order both inserted and deleted in the same order.

If I ran a multiple row update like update test set [value]='', I want the id pair to be consecutive, and if I run update test set [id]=0, I want the value pairs to be consecutive.

I want it to be in a generic way, to add auditing to a random table, and without a sorting that would slow down the DB unnecessarily.

The above example is working (usually?), but I have been told in this post that it is not guaranteed.

Is there a recommended way to do it?