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.
Have you had any luck creating a primary key in a MS SQL Server 2012 table and have QGIS recognize it? I can’t get QGIS to see my clustered primary key I created on a SQL table with geometry.
Thank you for this tip, this is an issue I have found very little information on. In addition to what you mentioned there is also an issue getting and the ObjectID recognized when publishing SQL Geometry from a View to GISOnline or Portal. Do you have suggestions for overcoming this?
Are you sure it isn’t just hiding the ObjectID when published? The ObjectID for ESRI objects is not a primary key. It could change at anytime. If you are publishing to ArcGISOnline or Portal and want to keep a key, make sure you create one for you dataset yourself, preferably in a new column.