question

hgfjunker avatar image
hgfjunker asked

Top In an Insert Statement

I'm having a bit of trouble understanding the TOP clause in an insert statement. On several MS sites it appears to state that if the TOP(n) clause is used, it will select a "random" set specified by "n" [MS Site][1]. However, that does not appear to be the case. Instead, this query returns an ordered recordset by ID: TRUNCATE TABLE #t
**insert top (5)**
into #t
output inserted.ID, inserted.facilityNo, inserted.portNo, inserted.endcode, inserted.cable
select *
from dbo.temp_data
The above result contrasts with the query below which actually returns a random ordered recordset: TRUNCATE TABLE #t
insert into #t
output inserted.ID, inserted.facilityNo, inserted.portNo, inserted.endcode, inserted.cable
**select top (5)** *
from dbo.temp_data
order by NEWID() What's the deal with this? Why include the TOP(n) clause as an option for the INSERT, when I could just use it within the select statement for either ordered or random? [1]: http://msdn.microsoft.com/en-us/library/ms189463.aspx
top
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
KenJ avatar image
KenJ answered
The article states "When TOP is used with INSERT, UPDATE, MERGE, or DELETE, the referenced rows are not arranged in any order and the ORDER BY clause can not be directly specified in these statements." This does not imply a random order, it simply doesn't apply any ordering to the set. You get your top(n) in the order they were read from disk. Typically, rows are read from disk in the order in which they are stored in an index, so a query that uses the index will be returned ordered by the same column(s) as the index without the query doing any "arranging of order," whether or not you have used a top clause). If a table doesn't have an index, but was written in an ordered fashion, it is still possible for the select results to be ordered by their initial sort. The top clause will not change this. If you really want a "random" sort, continue to order by newid
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.