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 the next article. Here is the performance comparison of the two options. Here is what I had said in my previous article-

“It is very interesting to see when Option 2 is used, ORDER BY is absolutely ignored and the data is inserted in any order.

In future articles, we will talk about performance for these queries. What are your thoughts on this feature? Have you used INSERT TOP(N) in your application?”

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

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.

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

Please refer the original post here for the complete script.

I will be happy to know what you think of this blog post. Please leave a comment in the section below. I will read each of the comment and I will publish your comment on this blog post with due credit. Once again, I find the questions related performance comparison very interesting.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Quest

SQL Index, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Data and Page Compressions – Data Storage and IO Improvement
Next Post
SQL SERVER – Improve Performance by Reducing IO – Creating Covered Index

Related Posts

9 Comments. Leave new

  • 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

    Reply
  • 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.

    Reply
  • Interesting to see how the results are diferent with the diferent placement of the TOP clause. Thanks for the examples.

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • /* 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’);

    Reply
  • Mohammed Mubarak
    February 14, 2011 6:23 pm

    Hi,

    I want clear picture of Execution Plan. plz do the needful.

    Regards,
    Mubarak

    Reply

Leave a Reply