Inverted TOP * selection

Question

Do you know when you have this huge log table and you just need to see the last lines of X to know what is happening at this time?

usually you can:

select top 100 * from log_table order by ID desc 

to show the 100 latest entries, but it will do the reverse order (of course, due to the DESC order), for example:

 100010 100009 100008 and so on.. 

but for simplicity, I would like to see notes about the order in which they were. I can do this by running this query:

 select * from( select top 100 * from log_table order by ID desc ) a order by a.id 

where I get my 100th order by desc identifier and then invert the result set. It works, but it seems that there is no need to run 2 to produce this result.

My question is: does anyone have a better idea about this? How to select the top at the end of the table?

EDIT: a plan to fulfill both queries: Alex seems to have a very good idea, but David is also right, there is only one choice and one kind

EDIT2: set statistics IO ON:

 (10 row(s) affected) Table 'sysdtslog90'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 12, lob physical reads 0, lob read-ahead reads 0. (1 row(s) affected) (10 row(s) affected) Table 'sysdtslog90'. Scan count 2, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 12, lob physical reads 0, lob read-ahead reads 0. (1 row(s) affected) 

Answer 1

If id indexed and consistent enough, perhaps the fastest way:

 select * from log_table where id > (select max(id) from log_table) - N 

Explicit order is still required to guarantee order.

Answer 2

but it seems that there is no need to run 2 to produce this result.

Wrong. It's necessary.

More details: see an approximate plan for fulfilling your request. It probably looks like ClusteredIndexScan -> Top -> only one Variety. The internal OrderBy query does not sort, it simply directs the execution to be read from the "back" table.