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. 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?
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
answered Apr 12 '12 at 08:37 PM