CLR Types - Pro SQL Server Internals - page 278

Although persisted computed columns can help with performance, they are increasing the size of the rows. You
are storing the same information several times, once as part of UDT binary value and in clustered and potentially
non-clustered indexes. They also introduce additional overhead for maintaining calculated columns when UDT is
frequently modified.
Keeping supportability and performance aspects in mind, you should be very careful when introducing CLR
user-defined types in your systems. The public methods of the type should be finalized before initial deployment, and
the code must be carefully tested. This will help to avoid situations when the type needs to be redeployed.
In addition, you need to minimize the number of CLR calls by creating and possibly indexing persisted calculated
columns, which store the values of the UDT properties and methods that are frequently called from the queries.

Spatial Data Types
SQL Server supports two data types to store spatial information: geometry and geography. Geometry supports planar,
or Euclidean, flat-earth data. Geography supports ellipsoidal round-earth surfaces. Both data types can be used to
store location information, such as GPS latitude and longitude coordinates. Geography data type consider the Earth's
roundness and provide slightly better accuracy, although it has stricter requirements for the data. For example, data
must fit in a single hemisphere and polygons must be defined in specific ring orientation. Client applications need to
be aware of these requirements and handle them correctly in the code.
Storing location information in a geometry data type introduces its own class of problems. It works fine and often
has better performance than a geography data type when you need to find out if a location belongs to a specific area
or if areas are intersecting. However, you cannot calculate the distance between points: the unit of measure for the
result is in decimal degrees, which are useless in a non-flat surface.

■ Coverage of spatial data type methods is outside of the scope of this topic. If you are interested in learning
more about this, check out this site for more details: http://msdn.microsoft.com/en-us/library/bb933790.aspx .

Note

Although spatial data types provide a rich set of methods to work with the data, you must consider performance
aspects when dealing with them. Let's compare the performance of the methods that calculate the distance between
two points. A typical use-case for such a scenario is the search for a point of interest (POI) close to a specific location.
As a first step, let's create three different tables that store POI information.
The first table, dbo.Locations , stores coordinates using the decimal(9,6) data type. The two other tables use a
geography data type. Finally, the table dbo.LocationsGeoIndexed has a Location column indexed with a special type
of index called a spatial index . These indexes help improve the performance of some operations, such as distance
calculations or ones that check to see if objects are intersecting. The code is shown in Listing 14-7.

Listing 14-7. POI Lookup: Creating test tables
create table dbo.Locations
(
Id int not null identity(1,1),
Latitude decimal(9,6) not null,
Longitude decimal(9,6) not null,
primary key(Id)
);

create table dbo.LocationsGeo
(
Id int not null identity(1,1),