Spatial Support for Persisted Computed Columns In Denali CTP1

  • Article

A few weeks ago, in the whitepaper, New Spatial Features in SQL Server Code-Named “Denali” Community Technology Preview 1, new spatial support for persisted computed columns was presented.  This post will elaborate on this new feature, describing how it is different from prior support in SQL Server 2008.

In SQL Server 2008 you could create a column in a table defined by a spatial method.  For instance, you could create a column to store and persist the area of a polygon using the STArea() method, for instance, as long as that method did not return a spatial type (Geometry or Geography):

CREATE TABLE LOCATION (
ID INT PRIMARY KEY,
GEOM GEOMETRY,
AREA AS GEOM.STArea() PERSISTED
);

Note that the computed column, AREA, will be of type FLOAT.

In SQL Server Denali CTP1, this functionality is supported and augmented with the ability to persist a computed spatial column (Geometry or Geography) in the table definition.  For example, lets say that you wanted to define the centroid of the polygon in the GEOM column in the table DDL.  In Denali CTP1 (but not in SQL Server 2008) you can define this as follows:

CREATE TABLE LOCATION (
ID INT PRIMARY KEY,
GEOM GEOMETRY,
AREA AS GEOM.STArea() PERSISTED,
CENTROID AS GEOM.STCentroid() PERSISTED
);

Note that the computed column, CENTROID, will be of type GEOMETRY.

Here is how this table looks in SSMS:

image

Technorati Tags: SQL Server,spatial,2008,Denali,CTP1,computed,persisted,column