It is always fun to receive a follow up comments when I write a blog post. Yesterday I wrote about SQL SERVER – Simple Query to List Size of the Table with Row Counts and here is the follow up question which I received about adding a NULL column.
Question: When I add a column to a table which is NULL and it does not contain any default value does it takes up additional space in the table?
Answer: No. Well Usually No. There are some special cases when the size of the table increases, but in the most cases, if we are adding a column which is empty it does not increase the size of the table.
Let us see a practical example.
First of all – please measure the size of the table using the script displayed in this blog post.
Now run following query, which adds an empty column of data type integer.
ALTER TABLE Sales.SalesOrderDetail ADD Col1 INT;
Now, once again run the script of measuring the space for the table from this blog post.
You can see after adding a column which is of type integer the space of the table is not increased at all.
Let me know if you have any different experience and I will publish it on the blog with due credit to you.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
