Consuming No space for storing NULL value in table

At 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.

FURTHER DETAILS:-

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.

EX:-

CREATE TABLE PersonInfo

(

PK_PRIMARY_KEY INT PRIMARY KEY,

FIRST_NAME VARCHAR(100) NULL,

LAST_NAME VARCHAR(100) NULL,

ADDRESS1 VARCHAR(100) NULL,

ADDRESS2 VARCHAR(100) NULL,

ADDRESS3 VARCHAR(100) NULL,

ADDRESS4 VARCHAR(100) SPARSE NULL,

CITY VARCHAR(50) NULL,

STATE VARCHAR(50) NULL,

RACE VARCHAR(100) SPARSE NULL,

RELIGION VARCHAR(100) SPARSE NULL

)

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 :

geometry, geography, image, ntext, text,timestamp, user-defined datatypes

Related Tags:
Database

Author: Monalisa Pradhan

Back to Top

Software Technology Tips

Database

Let us Connect!