QGIS – Only Showing One Attribute Record

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.