Sparse Column - Part 3 Column Sets

Column Sets

SQL 2008 provides column_set for all sparse columns which can be used as a single set for all sparse columns. It can be used to display values or to update/insert values also.

Let us see how.


create table test
( id int ,
person varchar(10),
salary int sparse,
department varchar(100) sparse,
details xml column_set for all_sparse_columns )

• There can be only one column set per table.

• You can not add column set to an existing table

insert into test(id,person,salary,department) values (1, 'person1','1000','dep1')
insert into test(id,person,salary,department) values (2, 'person2',null,null)

select * from test
ID Person Details
================================================================1 person1 <salary>1000</salary><department>dep1</department>
2 person2 NULL
================================================================

Here you can see all sparse columns are shown as single xml string under details column

Now if you want to see every sparse column in the result set you should provide all column name in select query

select id,person,salary,department from test

ID Person Salary Department
===============================
1 person1 1000 dep1
2 person2 NULL NULL
================================

• Simlar if we pass values in details column it will be also inserted into sparse columns

insert into test (id, person, details)values (3,'person3','<salary>3000</salary><department>dep3</department>')

select id ,person,salary, department from test where id = 3

ID Person Salary Department
===============================
3 person1 3000 dep3
================================

• Similarly if we update column set it will update sparse columns

update test set details ='<salary>4000</salary><department>dep3</department>'
where id = 3

select id ,person,salary, department from test where id = 3

ID Person Salary Department
===============================
3 person1 4000 dep3
================================


Here it has update salary from 3000 to 4000.

• However we need to provide all sparse column values in the update statement

• You can not update both column set and sparse column at same time