CODEWITHSUNDEEP
sql-serversql-server-2014
Jerry Dodge
Jerry Dodge

Reputation: 27276

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?

Upvotes: 0

Views: 135

Answers (1)

doganak
doganak

Reputation: 798

Does it make sense?

sp_helptext 'sp_rename'

Upvotes: 2

Related Questions