SQL SERVER – Performance Comparison – INSERT TOP (N) INTO Table – Using Top with INSERT

Recently I wrote about SQL SERVER – INSERT TOP (N) INTO Table – Using Top with INSERT I mentioned about how TOP works with INSERT. I have mentioned that I will write about the performance in next article. Here is the performance comparison of the two options.

It is clear that performance of the Insert TOP (N) is much better than the INSERT … SELECT TOP (N). However, please note that the result of both of the queries can be very different.

Please refer the original post here for complete script.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

11 thoughts on “SQL SERVER – Performance Comparison – INSERT TOP (N) INTO Table – Using Top with INSERT

  1. Excellent comparison.

    Need to check this in detail…you give a new way to consider this thing too.

    One question in my mind is:

    Is it same for Table variable OR Temp tables?

    Tejas
    SQLYoga.com

    Like

  2. Performance of 2nd query is better because SORT operation is not used to retrieve the records (pls see the execution plan). Both of these queries performance should be evaluated by removing order by clause.

    Like

  3. It is understood how the SQL Server deals with Insert Top (N) into..Select …. and Insert into…Select Top (N) and their performance as well… I dont think comparison here is needed since the results are different.

    I wanted to know why in the backgroud SQL Server is unable to Order By when Top is issued with Insert. I would really appreciate if you could clarify. Thanks much.

    Like

  4. This is the expected behaviour

    As per BOL

    TOP ( expression ) [ PERCENT ]
    Specifies the number or percent of random rows that will be inserted. expression can be either a number or a percent of the rows. The rows referenced in the TOP expression that are used with INSERT, UPDATE, or DELETE are not arranged in any order.

    Like

  5. /* Preserve the Order by enclosing SELECT code in EXEC without effecting performance. Query cost is for Option1 is 65% and for Option2 is 35% */

    — Option 1: Top with Select
    INSERT INTO InsertTestValue (ID)
    SELECT TOP (2) ID
    FROM TestValue
    ORDER BY ID DESC;
    GO

    — Option 2: Top with Insert
    INSERT TOP (2) INTO InsertTestValue1 (ID)
    EXEC (‘SELECT ID
    FROM TestValue
    ORDER BY ID DESC’);

    Like

  6. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 13 of 31 Journey to SQLAuthority

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #019 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s