x

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
more ▼

asked Apr 12 '12 at 07:38 PM in Default

hgfjunker gravatar image

hgfjunker
20 1 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Apr 12 '12 at 08:37 PM

KenJ gravatar image

KenJ
19.1k 1 3 11

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x10

asked: Apr 12 '12 at 07:38 PM

Seen: 473 times

Last Updated: Apr 12 '12 at 08:37 PM