Reduce NULL value space in SQL server using SPARSE

 

Microsoft introduced a new feature called SPARSE in SQL server 2008.This will help to reduce the space of column having high proportion of NULL values. It will help optimizing the SQL storage usage.

 

Where we can use this SPARSE?

              This can be only used column has a high percentage of NULL value in it. Let’s see an example, if you are storing data in fixed column length like int or bigint, once you save NULL in bigint its consume 8 bytes and most of the data are NULL in that column it should be huge storage space based on its data volume. So this can be resolved by creating SPARSE column.

 

CREATE TABLE Emp_table

( 

       ID int IDENTITY (1,1),

       First_Name VARCHAR(50) NULL,

       Last_Name VARCHAR(50) NULL,

       Emp_tagId BIGINT SPARSE NULL

)ON [PRIMARY]

 

From the above script, Emp_tagId is the SPARSE column created to eliminate storage space used by NULL, It will take zero storage space for NULL values

Where we can’t use SPARSE?

       If your column contain less NULL value or it is saving less than 50% of record NULL then it is not good to have use SPARSE because SPARS column consume extra 4 bytes than declared size,it is storing in special structure. Let assume in above example 70% Emp_tagId consists data and rest of them are non-NULL value then it will take total 12 bytes to save non-NULL values, Means 8 bytes for non-null value and 4 bytes for SPARSE.


What are the basic data types

            The data type defines the operations that can be done on the data, the meaning of the data, and the way values of that type can be stored. Sharing some basic data types and its usages.

1)TINYINT / SMALLINT/ MEDIUMINT/ INT / BIGINT
2) FLOAT / DECIMAL
3) CHAR / VARCHAR / TEXT
4) DATETIME / TIMESTAMP

TINYINT / SMALLINT/ MEDIUMINT/ INT / BIGINT

  • each of these handles integers, including negatives
  • differ on max values they handle + storage size
  • listed here in order of size (TINYINT is smallest)
  • generally, use the smallest that gets the job done

FLOAT / DECIMAL
  • FLOAT and DECIMAL handle decimals places
  • FLOAT is 'approximate'
  • DECIMAL is an exact data type
  • Use DECIMAL for dollars and cents to get better precision

CHAR / VARCHAR / TEXT
  • All of these data types store string values
  • CHAR and VARCHAR can handle up to 255 characters
  • CHAR(30): stores 30 characters, including trailing spaces
  • VARCHAR(30): UP TO 30 characters (no trailing spaces)
  • TEXT can handle ~65.5k characters (good for long strings)

DATETIME / TIMESTAMP
  • Both handle times, to the second
  • DATETIME: YYYY-MM-DD HH:MM:SS
  • TIMESTAMP: YYYYMMDDHHMMSS