To effectively store NULL values while consuming the least amount of storage space, SQL Server offers Sparse Columns. When NULL values appear in a column in a sizable portion of rows, sparse columns are the best option.
1. What Are Sparse Columns?
Sparse columns are ordinary columns optimized for NULL storage. When a column is declared as SPARSE, it does not consume storage for NULL values, making them beneficial when a large number of rows have NULLs.
- Benefits of Sparse Columns.
- Saves storage by not allocating space for NULL values.
- Reduces I/O operations and improves performance for sparse datasets.
- Supports filtered indexes for better query performance.
- Drawbacks of Sparse Columns.
- Non-NULL values take up more space than regular columns.
- It cannot be used with.
- Text, Ntext, Image, Timestamp.
- User-defined data types.
- Computed columns.
- Default values (unless explicitly specified in an insert).
- CHECK constraints (except NULL constraints).
2. Declaring Sparse Columns
To use sparse columns, declare them with the SPARSE attribute.
Example. Creating a Table with Sparse Columns.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
PhoneNumber VARCHAR(20) SPARSE NULL,
Address NVARCHAR(255) SPARSE NULL
);
PhoneNumber and Address will not consume storage when NULL.
When storing non-NULL values, they use more storage than regular columns.
3. Storage Considerations
The impact on storage depends on the data type.
- For NULL values: Storage savings are significant.
- For Non-NULL values: Sparse columns require an additional 4 bytes.
When to Use Sparse Columns?
- When at least 20-40% of values are NULL, sparse columns save space.
- If NULLs are less frequent, regular columns are more efficient.
Example of Storage Cost for INT Data Type.
4. Using Sparse Columns with Column Sets
SQL Server provides Column Sets to handle sparse columns dynamically.
Example. Using Column Set for Dynamic Queries.
CREATE TABLE EmployeeData (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
PhoneNumber VARCHAR(20) SPARSE NULL,
Address NVARCHAR(255) SPARSE NULL,
AdditionalData XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
);
AdditionalData (XML) aggregates all sparse column values into a single XML column dynamically.
Retrieving Data Using Column Set
SELECT EmployeeID, AdditionalData FROM EmployeeData;
The Column Set simplifies handling dynamic attributes.
5. Querying Sparse Columns Efficiently
Use Filtered Indexes to optimize queries on sparse columns.
Example. Creating a Filtered Index.
CREATE INDEX IX_Employees_PhoneNumber
ON Employees(PhoneNumber)
WHERE PhoneNumber IS NOT NULL;
This improves query performance for non-NULL sparse column searches.
Example. Query with Index Utilization.
SELECT Name, PhoneNumber
FROM Employees
WHERE PhoneNumber IS NOT NULL;
The filtered index ensures efficient lookups.
6. Checking Sparse Column Storage Space
You can analyze storage savings using sys.dm_db_index_physical_stats.
Check Space Savings.
SELECT name, is_sparse, max_length
FROM sys.columns
WHERE object_id = OBJECT_ID('Employees');
This shows which columns are SPARSE.
7. When NOT to Use Sparse Columns
Avoid sparse columns when:
- NULL values are less than 20-40% of total rows.
- The column is part of frequent aggregations.
- Additional 4-byte overhead is unacceptable.
8. Test Tables with sparse and without parse columns
Create two tables as below:
Add random data in both tables.
Check Table space.
In SQL Server, sparse columns are an effective technique to maximize NULL storage, minimize space consumption, and enhance performance. They function best when a large portion of the values are NULL and can be effectively queried with column sets and filtered indexes.
HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
