Software Technology Tips

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

Database

Let us Connect!

iso 9001 QA25 Red Herring STPI D&B Fastest Growing SME 2013 Award zinnov Nasscom

copyright (c) Mindfire Solutions 2007-2014. Login