Adding Object ID to your view

QGIS and ESRI both like to see ObjectID (or an identity) in the data returned from a SQL query.

I use two methods to accomplish this.

1. Row_number() Over

Select ROW_NUMBER() OVER ( ORDER BY anydatacolumn) as ObjectID

However, ESRI doesnt like the returned data type so I convert it to an INT like so:

Select CONVERT(int,ROW_NUMBER() OVER (ORDER BY anydatacolumn)) as ObjectID

2. Newid() or NewSequentialId()

This will return a MS UUID (GUID) or sequential GUID. Apparently ESRI doesnt mind GUIDS as a ObjectID. I have yet to test with QGIS.

Moving geometry accross linked servers with MSSQL

MSSQL implements geometry via the Common Language Runtime (CLR). This implementation doesn’t allow you to call these function across linked servers. The solution is to create a view and  convert the geometry into x,y values (or lat/long for geography).

Then convert your data back into geometry when exposing the data in a view on the destination.

 

Example:

SELECT x
,y
,GEOMETRY::STPointFromText
('POINT(' + CONVERT(VARCHAR(30), x)
+ ' ' + CONVERT(VARCHAR(30), y)
+ ')', 2927) AS shapegeometry