Ned Otter gives us some options for reducing the size of full backups:
SPARSE columns
IF a column contains mostly NULLs, then depending on the data type, you can achieve space savings by using the SPARSE property (documentation here). SPARSE columns can be used with filtered indexes to theoretically reduce storage space and increase query performance. But there are a boatload of gotchas, such as issues with query plan caching (filtered indexes), and the fact that if you use SPARSE columns, neither the table or indexes can have any form of compression (the documentation is clear about not supporting table compression, but does not mention index compression being an issue – but it is).
As the documentation clearly states, when converting a column from non-sparse to sparse, the following steps are taken:
- Adds a new column to the table in the new storage size and format
- For each row in the table, updates and copies the value stored in the old column to the new column
- Removes the old column from the table schema
- Rebuilds the table (if there is no clustered index) or rebuilds the clustered index to reclaim space used by the old column
For large tables with even a few columns that you wanted to convert to SPARSE, this process would take forever, because you must do this for each column you want to convert.
I don’t like sparse columns at all, but I do like the rest of Ned’s options.