10

I'm trying to create a table under SQL Server 2008 containing a GEOMETRY column and a calculated variation thereof.

Considering the following table where the calculated column returns a buffered geometry:

CREATE TABLE [dbo].[Test] (
    [Geometry]      GEOMETRY    NOT NULL,
    [Buffer]        FLOAT       NOT NULL,
    [BufferedGeometry] AS ([Geometry].STBuffer([Buffer])) PERSISTED
);

The problem with this is it results in the following error:

Msg 4994, Level 16, State 1, Line 2 Computed column 'BufferedGeometry' in table 'Test' cannot be persisted because the column type, 'geometry', is a non-byte-ordered CLR type.

I have search BOL and the web and can't seem to find a solution to my problem. I really would like it to be persisted so I can index it effectively. I could set it in code, but then I have the possibility of inconsistent data as I require both values at some point in time.

Anyone played with this and know a solution or workaround?

Update: Microsoft has added this functionality in SQL Server 2012.

Craig Nicholson
  • 1,241
  • 1
  • 11
  • 21
  • Vote for this Connect Item: http://connect.microsoft.com/SQLServer/feedback/details/378126/how-to-persist-a-calculated-geometry-or-geography-column – Cade Roux Apr 07 '11 at 01:03
  • The following does not work: `ALTER TABLE dbo.Period ADD [Interval] AS (geometry::STGeomFromText( 'LINESTRING (0 ' + CAST(CAST(DATEDIFF(SECOND, '19700101', StartDate) AS BIGINT) AS VARCHAR(20)) + ', 0 ' + CAST(CAST(DATEDIFF(SECOND, '19700101', EndDate) AS BIGINT) AS VARCHAR(20)) + '''', 0)) PERSISTED ;` – John Zabroski Nov 02 '18 at 17:41

2 Answers2

4

I guess you could use a trigger to calculate it and store it to the [BufferedGeometry] field

DJ.
  • 16,045
  • 3
  • 42
  • 46
1

Whoever is still having such problem: SQL Server 2012 now allows it

root
  • 2,327
  • 1
  • 22
  • 18