CODEWITHSUNDEEP
sqlsql-servert-sql
camelCaseWarrior
camelCaseWarrior

Reputation: 369

Does a table get deleted and re-created on alter table drop column?

Question is more specific to sql server and t-sql but would be interesting to know what happens across other db products.

Upvotes: 2

Views: 542

Answers (2)

Michael Goldshteyn
Michael Goldshteyn

Reputation: 74370

Not always, and SQL Server forces you to explicitly do this when it is necessary if you have a certain somewhat buried option turned on. This option is in:

Tools / Options / Designers / Table and Database Designers 
  "Prevent saving changes that requiere table re-creation"

Sorry for the shrunk image, SO seems to be doing this, but here is the item...

enter image description here

Upvotes: 3

Remus Rusanu
Remus Rusanu

Reputation: 294277

I have covered this subject at length in SQL Server Table Columns Under the Hood. The answer is no, a drop column does not re-create the table.

However, some tools may re-create the table as part of exposing a 'simplified' table management interface, like the SSMS table designer or schema diff tools, but that it really means that the tool is issuing the T-SQL to create a new table, copy the data, then drop the old table. And this is exactly the reason why I shun the use of all these tools, SSMS table designers included, and disfavor all diff based schema compare tools in favor of explicit upgrade scripts.

Upvotes: 2

Related Questions