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.
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 (http://blog.SQLAuthority.com)