By David Crowther
Question:
When I open my spatial data from a SQL Server Spatial Database, and I view the data in the Attribute Table why is there only 1 record?.... even though the table has multiple geometry features in the map window.
Answer:
This issue occurs when your source SQL Table does not have an INTEGER column with a unique value.
For example, here we have a SQL Table of Air Field Extents which has 5 records with 5 different geometries, but there is no column containing a unique value in INTEGER format.
When viewed in QGIS the 5 geometry features are shown in the map:
If we use the Identify Features tool we get individual records back for each feature that we click on:
However, if you use the Select tool and click on a map feature all objects are selected:
And if you Browse the Table, the Attribute window states there are 5 records , but 1 is filtered and 1 is selected:
This issue is caused as QGIS requires at least one unique column in Integer format in the source SQL Table – Note: this does not have to be set as the Primary Key.
So to resolve this, simply update your SQL table with a ROW ID using this query:
ALTER TABLE [dbo].[YOUR_TABLENAME]
ADD [ROWID] INT IDENTITY(1,1)
If you remove the layer from QGIS, refresh the Connection to SQL and re-load the SQL Table, the layer will now allow you to select the individual geometry in the map and show all records in the Attribute Table.