CONVERT_IMPLICIT when inserting a Geography datatype into a Geography datatype column on Temp Table

by Mazhar   Last Updated June 21, 2018 09:06 AM

@@Version
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?

DECLARE @xPolygon NVARCHAR(MAX) 
     = '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 Scaler 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?



Related Questions


Unable to remove additional tempdb file

Updated February 07, 2018 02:06 AM

TempDB data files don't shrink well on SQL 2008

Updated April 03, 2018 08:06 AM


Read GPS Coordinates into LineString from Table

Updated August 06, 2015 15:02 PM

Spatial Index Compression

Updated August 15, 2017 13:06 PM