Why is TOP operation in SQL execution plan After searching for a while, I decided to post this question for lack of finding an answer and apologize if there is a similar question/answer out there.

When running the query below on two similarly set up SQL servers, we encounter different execution plans which affects performance and we need help figuring out the cause.

The query:

SELECT process_id
INTO #temp
FROM revrep_revenue_fact
WHERE process_id = 284 
DROP TABLE #temp


Execution plan of server A


Execution plan of server B
Server B http://s2.postimg.org/z9fjrfv4n/server_B.png

You'll notice that server B has the TOP physical operation in the actual execution plan and we're trying to figure out why.  Both queries use the same index in the Index Seek.

Here are some details of server A and server B

Server A and B are both

Windows Server 2008 R2 Standard Service Pack 1

24GB RAM

64-bit operating system

SQL Server 2012 Versions obtained by using (SELECT SERVERPROPERTY('ProductVersion'))

Server A SQL version 11.0.3000.0

Server B SQL version 11.0.5058.0

What we've tried


Clearing procedure caches
Rebuilding indexes
Refreshing statistics
SET ROWCOUNT 0



Why does server B have the TOP in the execution plan?  In this simple query example there is no real issues but in a larger query the cost rises for the TOP and we see a performance hit.  Any help debugging this would be much appreciated and we can get you any additional information you may need to help.
Check to ensure the database compatibility level is the same on the 2 servers.  I ran a quick test on a SQL Server 2012 instance and see the TOP operator is introduced if the compatibility level is 100 or lower.  Unless you have a specific reason to do otherwise, it is best to use the 110 (SQL Server 2012) compatibility level on a SQL Server 2012 instance.