Search code examples
sql-serversql-server-2014

How does SQL Server internally rename a table?

Using Microsoft SQL Server 2014, we are performing some operations which require re-building a table structure. We have narrowed down our approach to two methods:

  1. Create new "temporary" table (Not the #TempTable kind), dump data into temp table, delete original, then dump the data back.
  2. Rename existing table, create new table, then dump the data from temp to new.

My colleague insists #1 is the ideal option to choose. However, I'm convinced that #1 will take twice as much time as #2 (since we have BLOB data consuming massive storage). Colleague claims that when you rename a table, internally, SQL Server creates a new table and dumps the data over anyway. I highly disagree. I'm sure that SQL Server just simply modifies the name of the existing table. While searching for how this actually works, I cannot find any information.

So how does SQL Server internally rename a table? Does it create a new table, dump the data, then delete the old one? Or does it simply modify the name?

Solution

  • Does it make sense?

    sp_helptext 'sp_rename'