Optimize your Storage while using NULL in your field with SPARSE Column.

If a column is defined with Sparse column then if the column hold null value, sparse column will take much lesser space as compared to regular column.

Few facts on SPARSE column

•        A column can be declared sparse by specifying the ‘SPARSE’ after the data type in the table.
•        A table can contain sparse columns of up to 30000. Though a table contains 30000 sparse columns the maximum number of non sparse columns in a table is still 1024 only.
•        If the value of a column is NULL, it doesn’t consume space/ lesser space at all. It stores the data in a single xml column but for an external application it behaves like a normal column.

Let’s Check out with an example, here we are creating two table one with sparse and second without sparse.

create table SpareTest

(id int sparse,

name varchar(10) sparse,

addrss varchar(15) sparse)

Command(s) completed successfully.

create table withoutsparse

(id int,

name varchar(10),

addrss varchar(15))

Command(s) completed successfully.

Lets try to insert few records in both the table

declare @i int = 1

while  @i <= 100000

begin

      insert into SpareTest values(null,null,null)

      insert into withoutsparse values(null,null,null)

      set @i=@i+ 1     

end

Now let’s see how much space used in both the table to store the null values

sp_spaceused 'SpareTest'

go

name       rows    reserved  data     index_size unused

------------------ --------  -------  ---------- --------

SpareTest  100000  1224 KB   1176 KB  8 KB       40 KB

sp_spaceused 'withoutsparse'

go

name       rows    reserved  data     index_size unused

------------------ --------  -------  ---------- --------

SpareTest  100000  1480 KB   1416 KB  8 KB       56 KB

Conclusion: Here we can easily compare, how much space are being saved when we are using Spare for storing null values.

Code burn; lighten world

Post Reference: Vikram Aristocratic Elfin Share