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?
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.