Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)

If I try to insert data into a geography datatype in a temp table I get a CONVERT_IMPLICIT, converting a Geography datatype to a Geography datatype?

     = '53.835134 -2.474670, 53.812436 -0.288391, 52.417944 -0.090637, 52.270286 -3.018494, 53.835134 -2.474670'

CREATE TABLE #xSpatialTable (
     id         INT IDENTITY (1,1) 
    ,GeogCol1   geography 

INSERT INTO #xSpatialTable (GeogCol1)
VALUES (geography::STPolyFromText('POLYGON(( ' + @xPolygon + '))', 4326));

The Second Compute Scalar operator in the actual execution plan for Expr1005 shows this

Scalar Operator(CONVERT_IMPLICIT(geography,[geography]::STPolyFromText((N'POLYGON(( '+[@xPolygon])+N'))',(4326)),0))

There is no such CONVERT_IMPLICIT if I change to using a user table instead of a temp table.

CREATE TABLE dbo.xSpatialTable (
     id         INT IDENTITY (1,1) 
    ,GeogCol1   geography 

INSERT INTO dbo.xSpatialTable (GeogCol1)
VALUES (geography::STPolyFromText('POLYGON(( ' + @xPolygon + '))', 4326));

Here's the actual execution plan

If I try the same test on a SQL 2014 database on the same machine, I don't get a convert_implicit whether I use a temp table or a user table.

Why the difference on 2008R2? and how do I get rid of the Convert_Implicit when using a temp table on 2008R2?

1 Answer 1


I don't think this has anything to do with the spatial extensions. I think this is this problem here,

From that post, all these work without generating CONVERT_IMPLICIT

SELECT * FROM [Table] WHERE IsSomething = 'TRUE';

SELECT * FROM [Table] WHERE IsSomething = Cast(1 as bit);

SELECT * FROM [Table] WHERE IsSomething = @TRUE;

You can read about the reasons for this problem here.

If it is the spatial type you may try a CAST to VARBINARY(max).. CAST( geography::STPolyFromText('') AS VARBINARY(max))

If you can't afford to upgrade to a newer version of SQL Server, PostgreSQL with PostGIS is perhaps an option as it is free and open source and has the best GIS implementation.

     GeogCol1   geography

INSERT INTO xSpatialTable (GeogCol1)
VALUES ( 'POLYGON((53.835134 -2.474670, 53.812436 -0.288391, 52.417944 -0.090637, 52.270286 -3.018494, 53.835134 -2.474670))' );
  • But why does the convert_implicit only occur with temp tables?
    – Mazhar
    Jun 22, 2018 at 8:21
  • I'm confused at how I could ever tell you why the engine has a different route leading to an implicit_coercion error without the source code to SQL Server 2005. I can only speculate. My speculation is that it wants you to cast your int to a bit (because that's what it is in the table) -- if that still generates the error on the temp table, try casting the geog/geom to a VARBINARY(max) if that still generates the error -- I'm out of ideas. Run to PostgreSQL. There I have the source code. We can only speculate, and I tried my best. ;) Jun 22, 2018 at 8:38
  • 1
    Ah, I didn't realise you were referring to SQL Engine code :-)
    – Mazhar
    Jun 22, 2018 at 9:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.