Overhead of ROW_NUMBER OR SEQUENCE

GokhanVarol 2013-07-05 20:56:52

I first used a sequence with defaults (should be cache size = 50) then sequence with 100K and then a row_number over (order by (select 0)), I was not happy with the performances then I also tested a select with discard results and insert into a heap with tablock and select into a table (last 3 do not have any sequence or row_number)
I did not try identity, from my past tests that was the worse in performance.

Adding a single sequence cheapest / select into (2,268,828 sec / 1,265,600 sec) = around %180. Is that expected, is there any more efficient way of generating sequences during an insert?
Thanks

alt text

link text