SQL SERVER – Observation – Adding a NULL Column and Table Space

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.

Solarwinds

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;

SQL SERVER - Observation - Adding a NULL Column and Table Space spaceafteraddingcolumn-800x559

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)

Solarwinds
, , ,
Previous Post
SQL SERVER – Simple Query to List Size of the Table with Row Counts
Next Post
SQL SERVER – How to Install SSMS From Command Line? Error: The Specified Value for Setting ‘MEDIALAYOUT’ is Invalid.

Related Posts

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

    Reply
    • 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.

      Reply
  • Kalman (Kal) Kernerman
    May 26, 2017 7:16 pm

    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

    Reply
    • I am totally with you Kal!

      Reply
      • Kalman (Kal) Kernerman
        May 26, 2017 8:28 pm

        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

  • Kalman (Kal) Kernerman
    May 27, 2017 1:31 am

    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

    Reply
    • 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.

      Reply
  • Gauri Shankar
    June 1, 2017 5:22 pm

    Nice sir easily understable..

    Reply

Leave a Reply

Menu