In SQL Server, 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 means that even if a field is populated with a null value, it still takes up the full space allocated to it.

Consider a scenario where you have a column called [Violation] in a table called [Employee], but very few employees have any violations. In this case, over 99% of the [Violation] field values are null. This is an example of a sparsely populated field.

To demonstrate a sparsely populated field, let’s create a simple table called dbo.Bonus. This table will have fields with fixed length data types, such as int and money. We will populate the table with some data.

CREATE TABLE dbo.Bonus
(
    BonusID INT,
    BonusAmount MONEY SPARSE
);

INSERT INTO dbo.Bonus (BonusID, BonusAmount)
VALUES (1, NULL), (2, NULL), (3, NULL);

Since all fields in the Bonus table contain fixed length data types, we can accurately calculate the space consumption per row. Each row of the Bonus table will consume 21 bytes, including the row header, fixed data, and null block.

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.

To analyze the space used by the Bonus table, we can use the sp_spaceused stored procedure. Running this procedure on the Bonus table shows that it contains three rows and its data has not yet exceeded its first 8 KB page.

Now, let’s add 997 more records to the Bonus table using a loop. The first record populated will be row 4, and the loop will continue until the BonusID value reaches 1000.

DECLARE @i INT = 4;

WHILE @i <= 1000
BEGIN
    INSERT INTO dbo.Bonus (BonusID, BonusAmount)
    VALUES (@i, NULL);
    
    SET @i = @i + 1;
END;

After adding 997 more records, the Bonus table now contains 1000 rows. Since each row occupies 21 bytes, these 1000 rows will take up 21 KB of space and should fit within three data pages.

Using the sp_spaceused stored procedure again, we can confirm the number of data pages used by the Bonus table.

Now, let’s explore the sparse data option in SQL Server. The sparse data option is a feature introduced in SQL Server 2008 for fields that are expected to be predominantly null. By using the sparse data option, you can instruct SQL Server to not have nulls consume space in sparsely populated fields.

To test this feature, let’s delete the Bonus table and recreate it with the same steps as before, but this time, we will create the BonusAmount field using the sparse option.

DROP TABLE dbo.Bonus;

CREATE TABLE dbo.Bonus
(
    BonusID INT,
    BonusAmount MONEY SPARSE
);

-- Insert data into the table

Now, the Bonus table contains 1000 records, and the BonusAmount field is a sparse field. However, we don’t see any difference in the appearance of the table or the data.

Let’s confirm the space usage of the Bonus table. We expect the space consumption to be less compared to the previous example where the sparse option was not used.

By using the sparse option, the consumption of the 1000 rows of the BonusAmount field is significantly reduced. Instead of occupying 24 KB of space, as in the previous example, the sparse field only uses the space required for the non-null values.

In conclusion, understanding and utilizing sparse data fields in SQL Server can help optimize storage space when dealing with sparsely populated columns. By using the sparse data option, you can reduce the space consumption of null values and improve overall database performance.

Don’t forget to participate in our special question for a chance to win a free subscription to Quiz Participants. Leave your answer in the comment section below with the correct option, explanation, and your country of residence. Every day, one winner will be announced from the United States and one winner from India.

Thank you for reading!