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.
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.
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.
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.
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.
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.
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.
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.
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?
- None
- 4000 bytes
- 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)
96 Comments. Leave new
The Correct Answer is Option – 1
Thanks
Narendra(India)
Hi Pinal,
The correct answer for the above question asked is Option #1
Explanation:
In the question you asked “How much space are the 1000 rows of the money field using?”
So we need only the space consumed by the money column as very nicely explained by you that money column with sparse option will never consume any space or no space will be allocated.
Hence the correct Option is 1 NONE
Diljeet Kumari
Country : India
Correct answer is: 1.
As the money field is created with Sparse option.
Thanks,
Basavaraj
India
Correct Answers is Option 1.
Ramdas,NC,USA
The answer is option 1 – none.
Pinal Dave rocks the Earth.
David
USA
Correct answer option 1: If a column is defined as sparse and the data is null it does take up any space.
Uday
USA
Answer: Option 1 : none
Rishi Divecha
Iselin, NJ, USA
Hi Pinal,
Challenge:
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
Correct Answer:
The correct answer is the 1st choice: None
Explanation:
When you created this Bonus table (for the question), you have two columns, consisting of a column with an INT Data Type and a column with a MONEY Data Type.
The column with the MoNEY Data Type is using the SPARSE option.
You have 1000 rows. In every row, the column with the MONEY Data Type contains NULL Values.
Since you are using the SPARSE option, SQL Server hasn’t assigned or allocated any space for the column with the MONEY Data Type with the SPARSE option, since all rows contain NULL Values for that column.
Country:
United States
Thanks for the knowledge!
Regards,
Bill Pepping
1.None
Gordon Kane
Allen TX
USA
option 3) true :since header 8*1000
byte store;
kkmjssate
Krishan Kumar mishra
india
The answer is 1
None Because the SPARSE specified column money is not allocated with any space by sqlserver….
USA
Answer is :
1.None
Vinay,
Pune
India
option 1 is the ans….none
when money column is defined as sparse the actual space occupied by money column wont be used as all the values are null….so option1 none is correct
Dharani
INDIA
Option
1.None.
Money fiels is set up with sparse optin and all money column are null.
Thanks
Shree
Bangalore India
correct answer : 1
According to this article, is none (1st option). If all records have NULL in the Money field and we set up our table with SPARSE option, the 1000 rows will use 0 bytes for Money field.
Country : USA
The correct Option for the Question 17 is #1 st option
Explanation:
Asked : How much space are the 1000 rows of the money field using?
Given : We have 1000 records and all instances of the money column are null. When you set up the money field, you used the Sparse option.
Here we require only the space consumed by the money column as just learned that money column with sparse option will never consume any space or no space will be allocated to it until it has some Data in it.
Hence the correct Answer is 1 = NONE
Dilip Kumar Jena
Country : INDIA
1. None – sparse fields that are empty consume no space
Nice article, will be able to use this to save space.
Thom Boiln
TX, USA
In Bonus table, 1000 rows of the money sparce column field with NULL data uses none space.
Answer: 1.None
SPARSE Keyword in a column definition is used to optimize the storage of values in that column. When the column value is NULL for any row in the table, the value requires no storage.
Do make a NOTE that “Sparce columns require more storage space for NON-NULL values than the space required for identical data that is not marked SPARSE. For ‘money’ data type required Nonsparse bytes = 8 and Sparse bytes = 12”
Check out link for estimated space savings by data type: https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-sparse-columns?view=sql-server-2017
Savitur Badhwar
Los Angeles, CA
USA
Correct Answers is Option 1.
A sparse column must be nullable.
Correct Answers is Option 1.
A sparse column must be nullable.
Richardson, tx