In the following examples, a document table contains the common set of columns DocID and Title. The Production group wants a ProductionSpecification and ProductionLocation column for all production documents. The Marketing group wants a MarketingSurveyGroup column for marketing documents.
A. Creating a table that has a column set
The following example creates the table that uses sparse columns and includes the column set SpecialPurposeColumns. The example inserts two rows into the table, and then selects data from the table.
.gif) Note: |
|---|
|
This table has only five columns to make it easier to display and read.
|
USE AdventureWorks;
GO
CREATE TABLE DocumentStoreWithColumnSet
(DocID int PRIMARY KEY,
Title varchar(200) NOT NULL,
ProductionSpecification varchar(20) SPARSE NULL,
ProductionLocation smallint SPARSE NULL,
MarketingSurveyGroup varchar(20) SPARSE NULL,
MarketingProgramID int SPARSE NULL,
SpecialPurposeColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);
GO
B. Inserting data to a table by using the names of the sparse columns
The following examples insert two rows into the table that is created in example A. The examples use the names of the sparse columns and do not reference the column set.
INSERT DocumentStoreWithColumnSet (DocID, Title, ProductionSpecification, ProductionLocation)
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27)
GO
INSERT DocumentStoreWithColumnSet (DocID, Title, MarketingSurveyGroup)
VALUES (2, 'Survey 2142', 'Men 25 - 35')
GO
C. Inserting data to a table by using the name of the column set
The following example inserts a third row into the table that is created in example A. This time the names of the sparse columns are not used. Instead, the name of the column set is used, and the insert provides the values for two of the four sparse columns in XML format.
INSERT DocumentStoreWithColumnSet (DocID, Title, SpecialPurposeColumns)
VALUES (3, 'Tire Spec 2', '<ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>')
GO
D. Observing the results of a column set when SELECT * is used
The following example selects all the columns from the table that contains a column set. It returns an XML column with the combined values of the sparse columns. It does not return the sparse columns individually.
SELECT * FROM DocumentStoreWithColumnSet ;
Here is the result set.
DocID Title SpecialPurposeColumns
1 Tire Spec 1 <ProductionSpecification>AXZZ217</ProductionSpecification><ProductionLocation>27</ProductionLocation>
2 Survey 2142 <MarketingSurveyGroup>Men 25 - 35</MarketingSurveyGroup>
3 Tire Spec 2 <ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>
E. Observing the results of selecting the column set by name
Because the Production department is not interested in the marketing data, this example adds a WHERE clause to restrict the output. The example uses the name of the column set.
SELECT DocID, Title, SpecialPurposeColumns
FROM DocumentStoreWithColumnSet
WHERE ProductionSpecification IS NOT NULL ;
Here is the result set.
DocID Title SpecialPurposeColumns
1 Tire Spec 1 <ProductionSpecification>AXZZ217</ProductionSpecification><ProductionLocation>27</ProductionLocation>
3 Tire Spec 2 <ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>
F. Observing the results of selecting sparse columns by name
When a table contains a column set, you can still query the table by using the individual column names as shown in the following example.
SELECT DocID, Title, ProductionSpecification, ProductionLocation
FROM DocumentStoreWithColumnSet
WHERE ProductionSpecification IS NOT NULL ;
Here is the result set.
DocID Title ProductionSpecification ProductionLocation
1 Tire Spec 1 AXZZ217 27
3 Tire Spec 2 AXW9R411 38
G. Updating a table by using a column set
The following example updates the third record with new values for both sparse columns that are used by that row.
UPDATE DocumentStoreWithColumnSet
SET SpecialPurposeColumns = '<ProductionSpecification>ZZ285W</ProductionSpecification><ProductionLocation>38</ProductionLocation>'
WHERE DocID = 3 ;
GO
| Important: |
|---|
|
An UPDATE statement that uses a column set updates all the sparse columns in the table. Sparse columns that are not referenced are updated to NULL.
|
The following example updates the third record, but only specifies the value of one of the two populated columns. The second column ProductionLocation is not included in the UPDATE statement and is updated to NULL.
UPDATE DocumentStoreWithColumnSet
SET SpecialPurposeColumns = '<ProductionSpecification>ZZ285W</ProductionSpecification>'
WHERE DocID = 3 ;
GO