A new column type with Sparse Column XML COLUMN_SET

Read Previous Post: Optimize your Storage while using NULL in your field with SPARSE Column.

There’s a new column type which is available to use with sparse columns – an XML COLUMN_SET. This is a column is only comes into picture when selected, and will return all the non-NULL sparse columns in a row as an XML BLOB.

It will also change the behavior of a SELECT * operation – removing all the sparse columns from the resultset, representing all the non-NULL sparse columns.

Let’s Check out with an example, here we are creating a table with sparse

create table SparseTest

(id int sparse,

name varchar(10) sparse,

addrss varchar(15) sparse,

sparseColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS)

Command(s) completed successfully.

Lets try to insert few null values in the SparseTest table few records in  table

declare @i int = 1

while  @i <= 3

begin

      insert into SparseTest(id,name,addrss) values(null,null,null)    

      set @i=@i+ 1     

end

Let’s insert few non null values in SparseTest table.

insert into SparseTest(id,name,addrss) values(1,'Gargi','Mumbai')

insert into SparseTest(id,name,addrss) values(1,'Bratiti','Banglore')

insert into SparseTest(id,name,addrss) values(1,'Lopamudra','Delhi')

insert into SparseTest(id,name,addrss) values(1,'Monalisa','Pune')

insert into SparseTest(id,name,addrss) values(1,'Pragayan',null)

Lets query the SparseTest to check out the result set.

select * from SparseTest

sparseColumns

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

NULL

NULL

NULL

<id>1</id><name>Gargi</name><addrss>Mumbai</addrss>

<id>1</id><name>Bratiti</name><addrss>Banglore</addrss>

<id>1</id><name>Lopamudra</name><addrss>Delhi</addrss>

<id>1</id><name>Monalisa</name><addrss>Pune</addrss>

<id>1</id><name>Pragayan</name>

(8 row(s) affected)

Here we can see it changes the behavior of a SELECT * operation – removing all the sparse columns from the resultset, representing all the non-NULL sparse columns, you can define column name in select statement to retrieve the column values, like this

select id,name,sparseColumns from SparseTest

id          name       sparseColumns

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

NULL        NULL       NULL

NULL        NULL       NULL

NULL        NULL       NULL

1           Gargi      <id>1</id><name>Gargi</name><addrss>Mumbai</addrss>

1           Bratiti    <id>1</id><name>Bratiti</name><addrss>Banglore</addrss>

1           Lopamudra  <id>1</id><name>Lopamudra</name><addrss>Delhi</addrss>

1           Monalisa   <id>1</id><name>Monalisa</name><addrss>Pune</addrss>

1           Pragayan   <id>1</id><name>Pragayan</name>

(8 row(s) affected)

When code burns from heart, it shapes to rebellion   

Post Reference: Vikram Aristocratic Elfin Share

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