SQL临时表备忘

Dear All,

I see a lot of use of temp table to say output a range of a records in database, but you don't need it at all. You can do:

 

say, you want list a table: 

 

select * from ccon_order_header_edi

 

and you want row 10 to 20:

 

SELECT * FROM 

(select row_number() over (order by orderid) rownum , * from ccon_order_header_edi) a

WHERE rownum BETWEEN 10 AND 20

 

 

Note that: row_number() over (order by orderid) is for generating the row number and all you need is to identify a field in your table for its order (in this case "orderid"). It is much more simple and don't need to generate/create a temp table.

 

So, I expect your stored procedure will be shorter/faster/easier to maintain.

 

Could you use this in all the new stored procedure and retrofit into old one when you need to change?