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