t the time of designing table, we create columns but later some of these columns are not used or rarely used. These columns always take NULL value. Storing NULL value in a column takes memory space leading to wastage of memory. To avoid this there is a new feature present in SQL Server 2008 i.e Sparse Column.
Storing NULL in a sparse column doesn’t take any memory space at all. This feature provides a highly efficient way of managing empty data in a database by enabling NULL data to consume no physical space. When you declare a column as Sparse and any time a NULL value is entered in the column it will not use any space.
In this example FIRST_NAME thorugh ADDRESS3 and CITY, STATE are required, ADDRESS4, RACE, RELIGION ARE rarely used. When creating the table for ADDRESS4, RACE, RELIGION we are using SPARSE option, since we know this date will be rarely stored.
N.B:- Sparse column not applicable for following data type :