In terms of SQL, what exactly is Query Layer's "Unique Identifier" doing?

Question

When you make a link with a database using Query Layer in ArcMap, you must select a Unique Identifier, which ensures that the resulting Layer only has unique records in the Attribute table.

In some cases, I have seen used Query Layers in such a way that the Unique Identifier asked by ArcMap is used to make sure that records are distinct (reducing from 4000 to 500 records for instance)

But in terms of SQL what exactly is it doing? Is it a "where" clause, selecting by distinct, selecting the min or the max of what it encounters? I know it's created an ObjectID based on the unique identifiers but I don't know how it's assigning that ObjectID to rows and deciding which rows are dropped from the attribute table.

Answer 1

I think you're misunderstanding the purpose of the unique identifier field, and using in ways that will generate undefined results.

When a Query Layer is defined, you are given a great deal of leeway to generate an SQL query. ArcGIS uses that query, with various constraints, to access the data. The most common constraint is a spatial filter clause (using the map canvas boundaries to reduce the feature set for rendering). But the query is also executed when the table associated with a feature class is opened, or when an Identify operation is performed, when a row in the feature table is highlighted, or when a spatial selection is made.

The purpose of the unique identifier is to maintain the linkage between the rows in the table object and the graphics on the map canvas. There is nothing special about the identifier column (which can be of INTEGER, STRING, UUID, or DATE integral types), except that it must be NOT NULL, and that it return a unique value.

ArcGIS enforces the uniqueness, but it can't prevent various forms of unsupported manipulation, like using an arbitrary row number (rownum) in the unique identifier. The reason this is unsupported is that it violates the purpose of the identifier: If you have spatial query for which rownum 1 meets the selection criteria, then you try to highlight rownum 1 in the business table, they are not likely to be the same row! The same flakiness applies if you UNION features together and use MIN() or MAX() on the rowid column or other sorts of actions which alter the intent to preserve 1:1 relationship between features and rows.

So, the unique identifier should be a genuine property of the row, and is expected to be repeatable in subsequent queries, no matter what WHERE clause is applied. If you are applying a DISTINCT operator, it's possible that you are setting yourself up for unexpected behavior (there will also likely be a performance cost). In no case does ArcGIS choose "which rows are dropped from the attribute table" -- this is always in the purview of the database.

Answer 2

To answer your question, the way it works is that the database will return one row for each of the distinct values, but there is no guarantee that every time you use the layer the same rows are returned. What row it latches onto is somewhat arbitrary, especially if people are editing the table in question.

Alternatives to the unique identifier that is not actually unique may vary depending on the actual query and what you really want the query to return. It depends on whether you need to join or filter tables, whether you need to edit and how static the data is.

If you only need a one-time copy of a single table, and you are lacking a unique identifier of the correct column type, you can use the Copy Rows tool. This will create a copy of the table with a new ObjectID column.

You could have the database administrator add a suitable unique identifier column to each table that needs to be joined in your query layer.

You could have the database administrator create a view table for you. This is a table compiled from a SQL query, so it can be joined and filtered. This would be doing the same thing you are trying to do with your query layer, except doing it on the database side. Views stay synchronized with the data they are pulling from, so they will stay current.

Quite frankly, it is possible that the person putting the query layer together doesn't know SQL very well. Maybe the query could be fixed to do what you want it to do. Perhaps they can get someone more knowledgeable to help them.

Hopefully, this will give you some ideas to discuss with your fellow staff members.