Dropping a Column as a Metadata Operation

Kevin Feasel

2019-08-29

T-SQL

Max Vernon takes us through column dropping:

Dropping a column that is not referenced by any other object lets the storage engine simply mark the column definition as no longer present. Deleting the meta-data invalidates the procedure cache. Any query that subsequently references the affected table will result in the plan for that query be recompiled. The recompile operation can only return columns that currently exist in the table. As a result, the storage engine skips the bytes stored in each page for the dropped column, as if the column no longer exists.

This has some nice benefits in practice around minimizing deployment-releated downtime.

Related Posts

Computing Time to Payment on Invoices

Daniel Hutmacher has a painful but realistic problem to solve: Here’s an example customer. You’ll notice right off the bat that we’re sending this customer an invoice every day on the 20th of the month. To add some complexity, the customer will arbitrarily pay parts of the invoiced amount over time, and to add insult […]

Read More

Tracking Who Changed Data

Bert Wagner is on a quest to find out who moved his cheese: Have you ever wondered who was the last person (or process) to modify a piece of data in your database? SQL Server offers plenty of system views and functions that provide insight into how your server is running and how your queries are performing. […]

Read More

Categories

August 2019
MTWTFSS
« Jul Sep »
 1234
567891011
12131415161718
19202122232425
262728293031