SQL Server Technical Note

Writer: Ed Katibah (Microsoft)

Technical Reviewers: Michael Kallay (Microsoft), Milan Stojic (Microsoft) and Michael Rys (Microsoft)

Published: November 2011

Applies to: SQL Server Code-Named “Denali”, SQL Server 2012, SQL Azure

Introduction
In Microsoft SQL Server 2012 (previously known as SQL Server Code-Name “Denali”), all constructions and relations are now done with 48 bits of precision, compared to 27 bits used in SQL Server 2008 and SQL Server 2008 R2. This can result in differences that range from how individual coordinates (vertices) in spatial objects appear (rounding) to how computational results are produced in different versions of the database server for certain spatial operations.

Recently, SQL Azure has been upgraded in all data centers to incorporate the new SQL Server 2012 spatial library. This upgrade, known as the September 2011 Service Release, thus enables spatial operations in SQL Azure to be the computationally identical with SQL Server 2012. 

Because of this new feature, results from spatial operations in SQL Azure September Refresh and SQL Server 2012 can differ from results produced in their respective prior versions. For most if not all cases, these changes will not be noticed and will not materially affect output results. There are, however, potential cases where this might matter. 

Coordinate Rounding

One side-effect of the precision change is that rounding of floating point coordinates for original vertices (points) not used in internal computation is minimized, in many cases not occurring at all. For example, consider the following vertex coordinates, which were processed using the STUnion() method in SQL Server 2008 but which were not involved in computation of the resulting geometry.

Vertex Coordinates                         Vertex Coordinates After Computation

82.339026 29.661245        82.339025999885052 29.662144999951124

In SQL Server 2012, the greater numerical precision assists in the preservation of original coordinates of input vertices, in most cases. Here is the result of the same STUnion() method in SQL Server 2012.

Vertex Coordinates                         Vertex Coordinates After Computation

82.339026 29.661245        82.339026 29.662145

Measurement Values

Length and area measurement value can vary between server versions. In the first case, length measurements for a series of highway routes in the United States, ranging from short to long, are illustrated. The results are shown in Table 1.

Table 1. Length measurements for several routes (lines) illustrating differences between server versions, using the STLength() method.

In the next case, area measurements for U.S. states, ranging from small to large, are illustrated. The results are shown in Table 2.

Table 2. Area measurements for U.S. states (polygons) illustrating differences between server versions, using the STArea() method.

Spatial Productions

Certain spatial productions, such as unions, can produce new spatial objects that may differ between the server versions in discussion here. If you compare such objects with a discrete measure, such as the number of points (vertices) that the object contains, you can see that their measurements can vary depending on the server ve:0in 5.4pt;width:63pt;background-color:transparent;">

-0.08

Table 2. Area measurements for U.S. states (polygons) illustrating differences between server versions, using the STArea() method.

DECLARE @g1 GEOGRAPHY = (SELECT GEOG FROM States WHERE NAME = 'California');
DECLARE @g2 GEOGRAPHY = (SELECT GEOG FROM States WHERE NAME = 'Oregon');
SELECT @g1.STUnion(@g2).STNumPoints();

This produces a new spatial object, which is the geometric union of the States of California and Oregon spatial objects.

 

The number of vertices is then calculated for the new object (STNumPoints). This produces the following results (Table 3).

	SQL Server 2008 R2	SQL Server 2012
Spatial Object	Number of Points	Number of Points
Union of California and Oregon	14854	15050

Table3. Number of vertices defining a unioned object.

The difference between the numbers of vertices used to define the two combined spatial objects is the result of additional artifacts (see next section). Nevertheless, the output of the spatial methods, @g1.STUnion(@g2).STNumPoints(), is different between the two versions of SQL Server.

Artifacts

The increased precision has also increased the possibility that certain spatial operations will produce spatial artifacts. In the example shown in Table 3 the primary difference between the resultant spatial objects is the number of artifacts produced. The artifacts objects are very thin polygons along the shared California/Oregon border (see Figure 2). 

Figure 2. Spatial artifacts resulting from a union operation between two adjoining polygons. The spatial objects inside the black box, along the Oregon-California border, illustrate the artifacts.

In SQL Server 2008, the lower computational precision did not produce as many of these artifacts on the same data. To give you an idea of the nature of these artifacts, 1 artifact polygon was measured at 30 meters long and .186 meters wide. In many cases artifacts can be attributed to differences in coordinates of the shared edge. This, in combination with the increased computational precision of the new server, can exacerbate the artifact issue.

Persisted Computed Columns

Persisted computed columns, created using spatial methods, can also introduce side effects caused by the change in precision models.

Consider the following scenario where a persisted computed column is created in SQL Server 2008 (including SQL Server 2008 R2) or in a version of SQL Azure prior to September 2011. Using the spatial production example, in a previous section, the following CREATE TABLE DDL is used.

CREATE TABLE LOCATION (
  ID INT PRIMARY KEY,
  GEOM1 GEOMETRY,
  GEOM2 GEOMETRY,
  NUMPOINTS AS GEOM1.STUnion(GEOM2).STNumPoints() PERSISTED
);

This table may now introduce NUMPOINTS column results that are different from the results shown in the Spatial Productions section, earlier.

Upgrading to SQL Server 2012 and the latest SQL Azure Instances
In SQL Server 2012, when a table with a potential backward compatibility issue is detected during the upgrade process, such as a computed persisted column using one or more spatial methods, that table is disabled. All disabled tables are clearly identified, allowing the database administrator to rebuild the tables and bring them back online.

In SQL Azure, because the upgrade process is a continual, ongoing process, the strategy of disabling tables was not considered a viable solution. Hence, it is important to understand when and where such issues can occur. To assist you with this effort, see Backward Compatibility on MSDN, which provides some strategies, including the new system view, sys.dm_db_objects_impacted_on_version_change.

Another document, Known Issues in SQL Azure Database, may also be useful to review.

Conclusion
Spatial support in SQL Server 2012 and SQL Azure Q2 2011 Service Release now use the same version of the spatial library. Application migration from prior versions of SQL Server and SQL Azure should not be effected by the new library, in most cases. Special attention, however, should be paid to persisted computed columns that are defined using spatial methods.