SQL Server Geography type creates multiple types for the same variable

Question

I am starting to use the Microsoft SQL Server Geography datatype. Everything is ok but there is a small problem I ran into.

I started by creating a table (ClientId is not actually primary) routes with 2 columns: ClientId (int) and Zone (geography)

CREATE TABLE [dbo].[routes]
(
    [ClientId] [int] NOT NULL,
    [Zone] [geography] NULL,
)

I ran the following script (302624121 is the newly created table):

SELECT
    o.id as 'Id', c.name as 'Name', t.name AS 'Type', 
    c.length AS 'Length'
FROM 
    sys.sysobjects as o 
INNER JOIN 
    sys.syscolumns AS c ON o.id = c.id 
INNER JOIN 
    sys.systypes AS t ON c.xtype = t.xtype 
WHERE 
    (o.id = 302624121)

and voilà, this is what I get:

302624121   ClientId    int 4
302624121   Zone    hierarchyid -1
302624121   Zone    geometry    -1
302624121   Zone    geography   -1

The zone has been created 3 times!!!!

Next I added a stored procedure to select data from the above table where a given point is contained within the client's geography.

Create proc [dbo].[up_RoutesSelectByGeography]
    @ClientId int,
    @Zone geography
as
begin
    SELECT [ClientId], [Zone]         
      FROM [dbo].[Routes]         
      where ClientId = @ClientId and [Zone].STContains(@Zone) = 1 
end

I ran the following query with the id of the procedure:

SELECT
    o.id as 'Id', c.name as 'Name', 
    t.name AS 'Type', c.length AS 'Length'
FROM 
    sys.sysobjects as o 
INNER JOIN 
    sys.syscolumns AS c ON o.id = c.id 
INNER JOIN 
    sys.systypes AS t ON c.xtype = t.xtype 
WHERE 
    (o.id = 334624235)

And I always get 3 types for the same variable:

334624235   @ClientId   int 4
334624235   @Zone   hierarchyid -1
334624235   @Zone   geometry    -1
334624235   @Zone   geography   -1

This issue is posing me a problem because i am not able to map the field name with a variable as I get the same variable name thrice.

Any light to what is happening? Which variables to map to my c#??

Not part of the question, but for what it is worth you should be using `sys.objects`, `sys.columns` and `sys.types`, not `sys.sysobjects`, `sys.syscolumns` and `sys.systypes` - the system views `sys.sys...` are included for backwards compatility only, and will be removed from future versions of SQL Server. — GarethD, Feb 12 '15 at 12:29

Dan · Accepted Answer · 2015-02-12T12:22:59.377

2

The problem is that hierarchyid, geometry and geography all have the same xtype value in the sys.systypes table:

select name, xtype, xusertype from sys.systypes where xtype = 240
/*
name          xtype   xusertype
------------- ------- ---------
hierarchyid   240     128
geometry      240     129
geography     240     130
*/

Hence, you get a cartesian product when joining this table to syscolumns in your query only by column xtype. To avoid this, include the xusertype column in the join:

select o.id as 'Id', c.name as 'Name', t.name AS 'Type', c.length AS 'Length' 
FROM sys.sysobjects as o 
INNER JOIN sys.syscolumns AS c ON o.id = c.id 
INNER JOIN sys.systypes AS t ON c.xtype = t.xtype AND c.xusertype = t.xusertype  -- here!
WHERE (o.id = 334624235)

edited Feb 12 '15 at 12:22

answered Feb 12 '15 at 12:17

Dan

10,480
23
49

Thank you Dan. That did it! Now I have the data correctly. – Albert Zakhia Feb 12 '15 at 12:35
"To avoid this, include the xusertype column in the join". Or, you know, stop using the deprecated system tables. DMVs were introduced in SQL 2005 (10 years ago as of this writing)! – Ben Thul Feb 12 '15 at 16:47

SQL Server Geography type creates multiple types for the same variable

1 Answers1