What happens behind the scenes when adding a nullable column in SQL Server? This question came up today. When you add a column that accepts null values to the end of a table what exactly happens? Does the table get fully rebuilt similar to what happens when you change column order?
No. 

This is a metadata only change in SQL Server 2008. See Size-of-data operations when adding and removing columns

SQL Server 2012 can also add non nullable columns with a default without having to update all rows however there are also some circumstances in 2012 in which adding a nullable column is no longer just a metadata change.