Consider the following example
CREATE TABLE Product
(
ProductID int IDENTITY PRIMARY KEY,
PRoductName varchar(100),
ProductAttrib1 int SPARSE NULL,
ProductAttrib2 varchar(50) SPARSE NULL,
ProductAttrib3 int SPARSE NULL,
ProductAttrib4 int SPARSE NULL,
ProductAttrib5 datetime SPARSE NULL
)
In the above table definition we have specified all the ProductAttrib columns as of type sparse. We can do DML manipulations on sparse columns in two ways. One is same as way we do for other columns like
INSERT INTO Product(PRoductName,ProductAttrib1,ProductAttrib4)VALUES(‘Product1’,30,75)
Another way to do DML operations like INSERT/UPDATE is by means of column set
COLUMN SETS
————–
A column set is XML type column which can created on the table and which combines all the sparse columns of a table into a structured output. Its like a calculated column and it gives the data in all the sparse columns in structured xml format. We can INSERT/UPDATE values of several sparse columns together by modifying value for single column set created.
As an example lets see the effect of creating a column set in our earlier table
CREATE TABLE Product
(
ProductID int IDENTITY PRIMARY KEY,
PRoductName varchar(100),
ProductAttrib1 int SPARSE NULL,
ProductAttrib2 varchar(50) SPARSE NULL,
ProductAttrib3 int SPARSE NULL,
ProductAttrib4 int SPARSE NULL,
ProductAttrib5 datetime SPARSE NULL,
AttributeConsolidated xml COLUMN_SET FOR ALL_SPARSE_COLUMNS
)
Now lets insert some values
INSERT INTO Product (PRoductName,AttributeConsolidated)
VALUES (‘Product1′,’30752010-02-25T17:30:00’),
(‘Product2′,’220115112’),
(‘Product3′,’TestVal1232010-03-06T22:45:14’),
(‘Product4′,’67kwjfw2010-02-11T06:33:00’)
Now lets see the inserted data
As you see in the above it retrives the value as XML in column set and shows the non null values of attributes instead of retrieving individual columns themselves. However, if you still want the values to be returned as individual column you can use query like below
SELECT ProductName,ProductAttrib1 ,
ProductAttrib2,
ProductAttrib3,
ProductAttrib4,
ProductAttrib5
FROM Product
UPDATE Product
SET ProductAttrib2=’kwjfw’
WHERE ProductID=4
See the output below.It didn’t affect other columns at all
i'm new to sparse columns. is there a way to have sparse columns under a root element? example: i'd need this "abc", but sparse columns give "abc"