Have you ever maxed out the SQL Server table column limit yet still needed more columns? Hopefully not considering SQL Server has a max limit of 1024 columns per table. But as I found out, it’s possible for someone to reach out and ask for even more. Sparse columns are an option to consider when you can’t get enough. Let’s take a look at what sparse columns are and how they can be used.
In the Midnight Hour
Sparse columns are an option if you are looking to save disk space or need more than the allotted 1024 columns in a table. You can see the most benefit if your table contains a high number of NULL values. With sparse columns, only values that are not NULL will take up disk space. This can help by reducing the amount of storage needed and also reducing the amount of data your queries will have to sift through.
With that said, Microsoft recommends considering sparse columns when space saved is at least 20%-40%. Tables are provided that show comparisons between non-sparse and sparse columns here. Note that not all data types are supported.
Configure Sparse Column
Let’s say we have a table for comic books. We may not always have a specific genre that fits which we expect will lead to a significant chunk of NULL values. For that reason, we could make Genre a sparse column like so:
CREATE TABLE ComicBook ( ComicID int PRIMARY KEY, Title varchar(100), IssueNumber int, Publisher varchar(50), PublicationDate date, Genre varchar(50) SPARSE NULL ); GO
If you find an existing column that could be set to sparse, you can run an ALTER to change it. If we wanted to change Genre column to SPARSE for an existing table:
Run our ALTER:
ALTER TABLE ComicBook ALTER COLUMN Genre varchar(50) SPARSE NULL;
We can see that the column is now Sparse.
Tables with sparse columns can max out at 30,000 columns compared to 1024. To do so, you’ll need a column set XML column to group the sparse columns together.
Imagine we have a table full of sparse columns. We could add our column set to the end of the table like this:
CREATE TABLE ComicBook ( ComicID int PRIMARY KEY, ... ComicColumnSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ); GO
More info from Microsoft on column sets can be found here.
Sparse Can be Good
Consider looking into sparse columns if you haven’t used them before. It may not be worth proactively trying to fit them in while your databases are running well. However, it’s worth keeping them in your toolbox as they could be the the solution to a problem you encounter in the future.
Thanks for reading!
One thought on “What are Sparse Columns?”