UNION ALL or other ways to return first row of result-set

I am writing a table valued function to geocode an address based on its Suburb, State and Postcode. I try to geocode the address using different methods, in order of decreasing accuracy:

• Exact match on unique suburb-postcode-state combination

• Exact match on unique suburb-postcode combination

• Exact match on unique suburb-state combination

• Exact match on unique postcode

• Approximate match by non-unique Postcode, where all Suburbs with this Postcode are within 5 km of one another.

(I am working with a geographical region where Suburb-Postcode-State relationships are all many-to-many. In other words, one Suburb can have multiple postcodes; one postcode can have multiple suburbs and may exist in different states.)

Following is an extract from the table-valued function:

```
ALTER FUNCTION [geocode].[tvfn_Customer_Suburb_From_Address]
(
@Suburb NVARCHAR(100),
@State NVARCHAR(100),
@Postcode NVARCHAR(100),
@Country NVARCHAR(100)
)
RETURNS TABLE
AS
RETURN
(

SELECT TOP 1 *
FROM (

-- Unique suburb-postcode-state combinations
SELECT s.Suburb_DID
,s.Suburb
,s.State
,s.Postcode
,Geocode_DID = 4 -- Exact match by unique Postcode, Suburb and State
,s.Geocode_Latitude
,s.Geocode_Longitude
FROM geocode.tSuburbs_XX s
INNER JOIN [geocode].[tGeocode_Methods] gm
ON s.Geocode_DID = gm.Geocode_DID

WHERE s.[Is_Active] = 1
AND s.[Suburb] = @Suburb
AND s.[State] = @State
AND s.[Postcode] = @Postcode
-- Only suburbs that are geocoded with methods that can be used for geocoding customers
AND gm.[Can_Use_For_VIP] = 1

UNION ALL

-- -- Unique suburb-postcode combinations
SELECT s.Suburb_DID
,s.Suburb
,s.State

If you must use a single query (as required by a single inline function), you can use one of the two options below (illustrated in my recent answer to Relating 2 tables with possible wildcards?):
Option 1

Use multiple APPLY clauses with a startup condition for each using an outer reference from a previous apply in the chain. The efficiency of this method depends on the presence of startup filters in the execution plan. Correct results are guaranteed, but plan shape is not.
Option 2

Add an extra column with a constant literal to each clause of the union e.g. [Priority] = 1 then add an ORDER BY [Priority] ASC at the TOP (1) scope. Efficient operation depends on the plan avoiding sorts.

On reflection, this is not what you want in this case, because one row from each option is required by the merge concatenation in the plan. Nevertheless, it is an option in more general situations (where the alternate inputs produce more than one row, and the first row at low cost).

In addition:
Option 3

Since you're only returning a single row, you could use a multi-statement table-valued function instead, with explicit logic to try each option in order (in separate queries), returning as soon as the first result is found. This is guaranteed to produce correct results efficiently.

Note

The current function is tecnhically nondeterministic; SQL Server could evaluate the union all in any order it chooses, potentially returning a lower-priority result before evaluating a higher priority one.

StackExchange Database Administrators Q#137599, answer score: 9