Feature request #10946
Non-integer key columns in MSSQL
|Pull Request or Patch supplied:||No||Resolution:|
|Easy fix?:||No||Copied to github as #:||19299|
I'm just trying to use QGIS as another GIS platform for our product suite (in addition to ArcGIS, AutoCAD Map, GeoMedia, ...).
We use MSSQL and Oracle as DBMS backend, and I have problems with MSSQL.
I would like to use a SELECT statement as data source. This seems to be possible with postgres, but it's not possible with MSSQL, because the "table=dbo.tablename"-part of the data source must have this exact format. In postgres it's possible to use "table=(SELECT ... FROM xyz)". I don't know if this is a bug or if I should create a feature request ;-)
For now it's ok to use a view instead of a SELECT statement as data source, and this works in principle ("table=dbo.viewname").
In this case I have to set "key=primarykeycolumn" in the data source, so that each row is identifiable.
Our primary key-columns are of type "char", and this does not work for the MSSQL (but it does for Oracle).
SQL Profiler told me, that "int" seems to be the required type for the key columns, because every SQL statement contains a "0" as primary key value.
I've also noticed that "STIntersects()" is used to query the currently visible elements - I think you should consider using "Filter()" to get (much) better performance.
#6 Updated by Ali Diba about 5 years ago
You should allow for supporting Global Unique ID which is a unique 16 byte number. This is refereed to as GUID and UNIQUEIDENTIFIER within the SQL Server community. GUIDs are very important when it comes to data collection from multiple disconnected devices. This is the only way to guarantee merging of the data from all devices while avoiding conflicts.