这个功能目前的定义仅是这样:
<column_set_definition> ::=column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
也就是说仅能用于sparse column,而按照以下的说法真能实现倒也是挺高兴的:
1-"You should consider using column sets when the number of columns in a table is large, and operating on them individually is cumbersome. Applications might see some performance improvement when they select and insert data by using column sets on tables that have lots of columns"
看这个似乎是为过多的例组织的,然而根据其定义,就能发现,这是为sparse column定义的.
2-"A column set is an untyped XML representation that combines all the sparse columns of a table into a structured output"
即使在其下的部份讲到column set可以应用于sql_variant,我以为是可以应用到含有sql_variant类型的例,不管是不是sparse,但是实际上column set只能应用于sparse columns.
例:
create table r(c1 sql_variant sparse,c2 sql_variant sparse,cs xml column_set for all_sparse_columns);
go
insert into r(c1) values(123);
insert into r(c1) values('456');
select * from r;
结果集如下:
<c1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" xsi:type="sqltypes:int">123</c1><c1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" xsi:type="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlSortId="52" sqltypes:sqlCollationVersion="0" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:maxLength="8000">456</c1>
看着这些结果还是挺累人的,其实就123和'456',不过XML给你写上了类型