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)
9 Comments. Leave new
Hi Dave ,
Could you please tell if your post doesn’t contradicting sql-server-citation
!/2009/12/common-mistakes-in-sql-server-part-4.html
As they state that NULL does take space in a table .
Thanks
Kal
Good Point,
I will have to study this and ask my dear friend Hemant to help me understand more.
It is quite possible he is correct or there is gap in understanding.
Thank you Dave for the fast reply , from my testing and knowledge NULL does take space , maybe there are special cases (some data types like VARCHAR ) when it doesn’t , but for fixed size it seems like NULL will cost us some space .
Kal
I am totally with you Kal!
I found why it shows no differences in space in your query before and after adding a field with a NULL Value .
Please run the below command :
UPDATE Sales.salesOrderDetail SET Col1 = NULL
You will see that after running the above your USED_MB will grow .
I can explain that behavior as it expected , but I’m sure you can explain this even better then me :-) .
Cheers ,
Kal
Last reply with the answer :-) .
If you will run DBCC IND before and after adding the new field you will see that the number of pages wasn’t changed .
So the new row (Col1) with default value of NULL wasn’t exceeding page size (8k) on the row and was added to the existing pages – so total data size wasn’t changed .
But if you will examine one of the pages before adding the new INTEGER NULL value field and execute DBCC PAGE command before and after you will see the page size now is bigger .
So NULL values do take space , but as adding any other field that the field value will not exceed current page size and will not split or create new pages the total data size will stay the same . Hope this explain the issue .
Thanks
Kal
Hi Kal,
Thanks for adding this value. I think I learned my lessons here. I need to re-blog adding your details (with Due Credit).
Thanks again for taking time and providing input which helped me to learn something.
Nice sir easily understable..