Database Reference
In-Depth Information
7.
Now, let us update the ProductDesc column with an XML value using the
following statement:
UPDATE dbo.SparseDemo SET ProductDesc='<Color>Blue</Color>
<SellDate>2010/12/01</SellDate> WHERE ProductID=201
8. From the previous statement, we have specified the
ProductColor
and
SellDate
,
but not
ProductWeight
. In this case, the value will be
NULL
.
9.
When the sparse columns are explicitly referred to in the
UPDATE
statement, the
other column values will not be changed:
UPDATE dbo.SparseDemo SET ProductColor='Black' WHERE ProductID=201
GO
SELECT ProductID, ProductName, ProductColor,ProductWeight,SellDate
from dbo.SparseDemo
10. When the table has many sparse columns, then using the XML string to populate
data will be more useful than having a statement for an individual column.
This represents the wide-tables concept where the sparse columns allow a large number of
columns to be defined for a table (by default 30,000 sparse columns are allowed in a table).
How it works...
Defining a column as
SPARSE
is as easy as adding a relational column using a default
DML statement. Most of data types are allowed, except the image, ntext, text, timestamp,
geometry, or user-defined data types.
Any sparse columns that are not referenced in a DML operation will be set to a
NULL
value.
Once a column set is defined for a table, performing the
SELECT
*
query will no longer return
each individual sparse column. When updating a column set using an XML value, you must
include values for all the columns in the column set you want to set, including any existing
values. Any values that are not specified in the XML string should be set to
NULL
.
Designing spatial data storage methods
The support of SQL CLR allows applications to manage rich user-defined data such as
mathematical or geographical based data. SQL Server 2008 enables the native support of
.NET CLR data types by providing two data types: GEOMETRY and GEOGRAPHY.
Managing a spatial type of data will require high-performance and high-end hardware with
a massive data storage solution. However, SQL Server features on these two new data
types have enabled the applications to support various methods that will allow for creation,
comparison, analysis, and spatial data retrieval. In this recipe, we will go through the process
of designing spatial data storage methods using SQL Server 2008 R2.
Search WWH ::
Custom Search