Database Reference
In-Depth Information
columns; it can be used only in variables in T-SQL code. Programming in
SQL Server is beyond the scope of this topic, but the table data type plays
an important role in user-defined functions, which we discuss shortly.
Table variables behave in the same way as base tables. They contain
columns and can have check constraints, unique constraints, and primary
keys. As with base tables, a table variable can be used in SELECT, IN-
SERT, UPDATE, and DELETE statements. Like other local variables,
table variables exist in the scope of the calling function and are cleaned up
when the calling module finishes executing. To use table variables, you de-
clare them like any other variable and provide a standard table definition
to the declaration.
Hierarchyid
The hierarchyid data type is a system-provided data type that allows you to
store hierarchical data, such as organizational data, project tasks, or file sys-
tem-style data in a relational database table. Whenever you have self-
referencing data in a tiered format, hierarchyid allows you to store and
query the data more efficiently. The actual data in a hierarchyid is repre-
sented as a series of slashes and numerical designations. This is a special-
ized data type and is used only in very specific instances.
Spatial Data Types
SQL Server 2008 also introduces the spatial data types for relational stor-
age. The first of the two new data types is geometry, which allows you to
store planar data about physical locations (distances, vectors, etc.). The
other data type, geography, allows you to store round earth data such as lat-
itude and longitude coordinates. Although this is oversimplifying, these
data types allow you to store information that can help you determine the
distance between locations and ways to navigate between them.
User-Defined Data Types
In addition to the data types we have described, SQL Server allows you to
create user-defined data types. With
user-defined data types,
you can
create standard columns for use in your tables. When defining user-
defined data types, you still must use the standard data types that we have
described here as a base. A user-defined data type is really a fixed defini-
tion of a data type, complete with length, precision, or scale as applicable.
For example, if you need to store phone numbers in various tables in
your database, you can create a phone number data type. If you create the
Search WWH ::
Custom Search