Dbcc Traceon

Howdy folks! Long time no write. In this article, I will be answering a couple of questions from the previous posts about dropping columns. A Quick Refresh If you haven't read the previous posts on this topic, here is the list: What happens when we drop a column on a SQL Server table? Where's my space? Identify Tables With Dropped Columns How much Space can I expect to recover from a rebuild after dropping a column?

Introduction Page splits are one of those silent performance killers in SQL Server that many DBAs have heard about but rarely analyze in depth. On the surface, inserts might look harmless – rows keep getting added, queries return results, and no errors appear. But underneath, SQL Server is constantly managing how data is physically stored on 8KB pages, and each insert has the potential to fragment your index and degrade performance over time. Inserts at the end of a table generally work smoothly.

Short answer: The column is marked as 'deleted' and will stop being visible/usable. But, most importantly - The record/table size will remain unchanged. A metadata operation Dropping a column is a metadata/logical operation, not a physical one. This means that the data isn't removed/overwritten by this action. As Paul Randal mentions here: "the cost of that will be deferred for the inserters and not for the deleters". Sort of.