Feature request #10946

Non-integer key columns in MSSQL

Added by Markus Woehling almost 3 years ago. Updated about 2 months ago.

Status:Open
Priority:Normal
Assignee:Nathan Woodrow
Category:Data Provider/MSSQL
Pull Request or Patch supplied:No Resolution:
Easy fix?:No

Description

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.

Markus


Related issues

Related to QGIS Application - Bug report #9007: MS SQL uniqueidentifier data type is not supported Closed 11/04/2013
Related to QGIS Application - Feature request #12324: QGIS 2.8 Wien - Crashed on SQL Server Spatial Layers Closed 03/05/2015
Duplicated by QGIS Application - Bug report #12837: QGIS Crashing When Connecting to datasets in MSSQL Closed 05/28/2015

History

#1 Updated by Jürgen Fischer almost 3 years ago

  • Tracker changed from Bug report to Feature request

#2 Updated by Nathan Woodrow almost 3 years ago

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.

Can you open a new ticket for this.

#3 Updated by Markus Woehling almost 3 years ago

I've opened two new tickets #11076 and #10948

#4 Updated by Jürgen Fischer over 2 years ago

Support for arbitrary keys was added to the postgresql provider in ce4fc1da. The oracle provider also implements pktFidMap type keys.

#5 Updated by Nathan Woodrow about 2 years ago

  • Assignee set to Nathan Woodrow

#6 Updated by Ali Diba almost 2 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.

#7 Updated by Giovanni Manghi about 2 months ago

  • Easy fix? set to No

Also available in: Atom PDF