'What else should I consider when determining which columns in a database table to make sparse?
We have a table that has a lot of columns with numerous datatypes. The nullable columns consist of varchar(8000)'s, ints, floats, datetimes, and more (which is another issue but for now it is what it is). I did an analysis and determined that the table currently has mostly nulls in the int and datetime columns (about 80% null for each, which according to https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-sparse-columns?view=sql-server-ver15 should be ok, since they suggest 64%). In my tests, I created a version of the same table, but made the ints and datetimes sparse nulls. Then, I inserted what was in the original table into the sparse table, and my results on space seem worse. The reserved, index_size and unused are bigger, while the data is smaller. Is there anything else to consider besides percentage of nulls that could cause the sparse tables to be worse? Or am I misunderstanding how sp_spaceused works?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
