If I remember correctly, in Oracle everything is a cursor behind the scenes, even if you don't explicitly declare one.  See the Handling Explicit Cursors section in this page:

I know it's been that way since at least SQL Server 2000.  I haven't worked with any version before that.

Depending on where that 120 GB is in your data file, you could potentially be able to use the TRUNCATEONLY functionality of DBCC SHRINKFILE
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql
This makes it only...

If the database engine has bad statistics, then yes it could easily choose an execution plan that is not optimal.  You can look at the stats for a table fairly...

Do you have SQL Server Agent service running?  If so, it might be configured to automatically start the engine service if it's not running:
https://technet.microsoft.com/en-us/library/gg313742.aspx

I think we're still missing something, as SELECT * would return all 4 rows, not just 2, and it's not entirely clear other than you want rows that have different...

Using either the ALTER SCHEMA TRANSFER or SYNONYM methods, blocking would occur if someone is actively querying the current live table and you're trying to repoint to the new table.