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?”
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 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)
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
Yes. It has the same effect for temp tables and table variables
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.
Interesting to see how the results are diferent with the diferent placement of the TOP clause. Thanks for the examples.
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.
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.
/* 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’);
Hi,
I want clear picture of Execution Plan. plz do the needful.
Regards,
Mubarak