As database developers and/or administrators, we often encounter the need to make schema changes to tables. These changes involve various operations, such as adding or removing columns.

Understanding the inner workings of these operations may be crucial. Why are some changes quick and seamless, while others can significantly impact system performance?

In this session, we'll delve into SQL Server internals to explore the processes involved in adding and removing columns. We'll examine the intricacies of these operations, including their utilization of the transaction log, the locking system, and space management. This exploration will unravel their impact (or lack thereof) on system resources and performance.

Is adding a new column always a slow and a blocking operation? And what about dropping a column? Will this single action immediately reclaim the used space? Let's explore these and other questions together.

By gaining insight into these specific examples, you will be better equipped to manage similar schema changes effectively in your database environments.