SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Sparse Data and Space Used by Sparse Data – Day 17 of 35

Answer simple quiz at the end of the blog post and –
Every day one winner from India will get Joes 2 Pros Volume 3.
Every day one winner from United States will get Joes 2 Pros Volume 3.

Sparse Data

Fields with fixed length data types (e.g., int, money) always consume their allotted space irrespective of how much data the field actually contains. This is true even if the field is populated with a null.

Occasionally you will encounter a column in your database which is rarely used. For example, suppose you have a field called [Violation] in a table called [Employee] but very few employees have any violations – perhaps two or three for every 1000 employees. In this case, over 99% of the Violation field values are null. This would be an example of a sparsely populated field.

To demonstrate a sparsely populated field, we will create a simple table with the design shown here. Create and populate the dbo.Bonus table by running this code in the figures below.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Sparse Data and Space Used by Sparse Data - Day 17 of 35 j2p_17_1

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Sparse Data and Space Used by Sparse Data - Day 17 of 35 j2p_17_2

Please note that INSERT statements demonstrated will use row constructors, which debuted as a new feature in SQL Server 2008. However, readers who are running SQL Server 2005 must write their INSERT statements using the alternate syntax shown in the lower portion of the last Figure.

Now look at all the records in the table. Since all fields contain fixed length data types (i.e., int, char(2), money), we could have accurately calculated the per row consumption before we even added any data to the table.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Sparse Data and Space Used by Sparse Data - Day 17 of 35 j2p_17_3

Each row of the Bonus table will consume 21 bytes based on the data below.

  • Row Header – 4 bytes (true for all rows).
  • Fixed Data – 14 bytes (BonusID is an int (4 bytes) and BonusAmount’s data type is money (8 bytes).
  • Null Block (aka Null Bitmap) – 3 bytes

The nulls in the money field do not change the space consumption because fixed length data types always use the full amount of space allocated to them. At 21 bytes per row, 1000 rows of the Bonus table would require 21,000 bytes and fill up about 3 data pages (1 data page = 8060 bytes). Currently there are only three records in this table so just 1 data page of 8K has started to fill up.

Analyzing Space Used

A handy tool for checking the storage amount which an object occupies is sp_spaceused. The figure below shows the Bonus table passed into this stored procedure. We see the Bonus table contains three rows and its data has not yet exceeded its first 8 KB page.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Sparse Data and Space Used by Sparse Data - Day 17 of 35 j2p_17_4

If you added enough records the data space used would need more memory pages. Let’s run a loop to add 997 more records to the Bonus table. The first record populated will be row 4. This loop increments each subsequent BonusID value by 1, and the loop runs as long as the BonusID value is <=1000. Once the row containing BonusID 1000 has been entered into the table, the loop will end. One run the Bonus table contains 1000 records.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Sparse Data and Space Used by Sparse Data - Day 17 of 35 j2p_17_6

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Sparse Data and Space Used by Sparse Data - Day 17 of 35 j2p_17_7

Since one row occupies 21 bytes, we know these 1000 rows will take up 21 KB of space and should fit within three data pages.

[21000 bytes/(8060 bytes/page) = 2.61 pages]

Since you can use half a data page SQL dedicated 3 data pages to the Bonus table. Let’s rerun the sp_spaceused sproc and confirm the number of data pages.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Sparse Data and Space Used by Sparse Data - Day 17 of 35 j2p_17_8

Using the Sparse Data Option

The sparse data option is a new SQL Server 2008 feature for fields you expect to be predominantly null. Using the sparse data option, you can instruct SQL Server to not have nulls consume space in sparsely populated fields.

To test this we’re going to delete the Bonus table and then re-create it using the same steps we took previously. The only difference will be that the BonusAmount field will be created using the sparse option. Recall we expect the BonusAmount field to contain very little actual data – most records will be null.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Sparse Data and Space Used by Sparse Data - Day 17 of 35 j2p_17_9

Recall we expect the BonusAmount field to contain very little actual data – most records will be null. The Bonus table now contains 1000 records and BonusAmount is a sparse field, but so far we don’t see any difference in the appearance of the table or the data.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Sparse Data and Space Used by Sparse Data - Day 17 of 35 j2p_17_10

Now let’s review how much space the Bonus table is consuming. Let’s confirm the space usage is less in this example. Had you not used the sparse option the consumption would have been 24KB like in our last example.

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - Sparse Data and Space Used by Sparse Data - Day 17 of 35 j2p_17_11

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLArchChapter4.0Setup.sql script from Volume 3.

Question 17

You have two fields, of INT and MONEY, in your Bonus table. You have 1000 records and all instances of the money column are null. When you set up the money field, you used the Sparse option. How much space are the 1000 rows of the money field using?

  1. None
  2. 4000 bytes
  3. 8000 bytes

Do not forget to participate in special question over here: Pluralsight Giving Away Free Subscription to Quiz Participants

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 3.
Winner from India will get Joes 2 Pros Volume 3.
The contest is open till next blog post shows up at which is next day GTM+2.5.

Reference:  Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – System and Time Data Types – Day 16 of 35
Next Post
SQL SERVER – Geography Data Type – Calculating Distance Between Two Points on the Earth – Day 18 of 35

Related Posts

96 Comments. Leave new

  • Answer is #1 None.

    Tricky, had to think about that some, but if you are just asking for the amount of space for that column which has all nulls and not the entire table, then the answer would have to be none since SPARSE was used.

    USA

    Hope I’m not too late, my post from earlier today isn’t in the list (I must have forgotten to click post?) I just remember that I forgot my country and came back in.

    Reply
  • Answer is option 1
    India

    Reply
  • You have two fields, of INT and MONEY, in your Bonus table. You have 1000 records and all instances of the money column are null. When you set up the money field, you used the Sparse option. How much space are the 1000 rows of the money field using?

    None
    4000 bytes
    8000 bytes

    Solution :

    The correct answer is None. When the money column is created with sparse option and all the 1000 rows are Null then no space is used. This is the property of sparse columns.

    The second and third options are wrong as they indicate some space occupied which doesn’t apply for sparse columns with all Null values.

    Sreeram
    Indian

    Reply
  • Rajesh Mohanrangan
    August 18, 2011 5:29 pm

    Question 17

    Ans : None

    Rajesh
    From India

    Reply
  • Answer is option 1

    Regards
    Rajesh
    From India

    Reply
  • Hi Pinal,
    The correct answer is option 1 which is None.

    Ques:-You have ask how much space is used by the money column when sparse is used in the Bonus table?
    Explanation:-Already you have mentioned in the blog that when any column which contains NULL values of a table if sparse is applied then we are instructing SQL Server to not consume space .

    Thanks,
    Manik Dey
    India

    Reply
  • Answer::: 3 (8000 bytes)

    here we have two fields one INT and money( sparce)
    so here 1000 rows inserted, and money is null
    so each row take 4 bytes(header)+4 bytes(Int)+0(money sparse)=8 bytes
    total rows=1000
    total space=1000* 8=8000 bytes

    Reply
  • Answer::: 3 (8000 bytes)

    here we have two fields one INT and money( sparce)
    so here 1000 rows inserted, and money is null
    so each row take 4 bytes(header)+4 bytes(Int)+0(money sparse)=8 bytes
    total rows=1000
    total space=1000* 8=8000 bytes

    thanks,
    Bhasker, USA

    Reply
  • Answer: 1
    The correct answer is none (1st option). If all records have NULL in the Money field and if table column set up with SPARSE option, the 1000 rows will use 0 bytes for Money field.

    Country: India

    Reply
  • Q 17) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Sparse Data and Space Used by Sparse Data – Day 17 of 35

    A.) A

    Winner from USA: Savitur Badhwar

    Winner from India: Santosh.S

    I thank you all for participating here. The permanent record of this update is posted on facebook page.

    Reply
  • Hello

    believe answer 1 (none) is the correct choice here. The money field will not add to the space used because it is a null with sparse option

    Eric
    (USA)

    Reply
  • Answer: Option 1

    But the nulls in the money field do not change the space consumption,
    because fixed length data types always use the full amount of space allocated to them.

    Without Sparse:
    ——————–
    CREATE TABLE BONUS
    (
    BONUS_ID INT NOT NULL,
    BONUS_AMOUNT MONEY NULL
    )
    GO

    DECLARE @ID INT
    SET @ID = 1
    WHILE @ID <= 1000
    BEGIN
    INSERT INTO BONUS VALUES (@ID, NULL)
    SET @ID = @ID + 1
    END
    GO

    EXEC sp_spaceused 'BONUS'

    OUTPUT: BONUS 1000 23688 KB 23680 KB 8 KB 0 KB

    Reply
  • Ans : option 1 None

    sudhir
    Chennai

    Reply
  • NICE INFO. THANKS FOR SHARING SIR

    Reply

Leave a Reply

Menu