I introduced a bug in a SQL script I wrote recently because I was unaware that SQL union did not guarantee the order of the returned result set.

The SQL stored procedure finds the primary and secondary network nodes that match a set of criteria. It looks something like:

select bd.name, n.nodeId, r.realmName from BuildDefaults bd join NetworkNodes n on bd.nodeId = n.nodeId join NetworkRealms r on cn.realmId = r.realmId where bd.typeId = @type and bd.locationId = @location union select bd.name, n.nodeId, r.realmName from BuildDefaults bd join NetworkNodes n on bd.nodeId = n.nodeId join NetworkRealms r on n.realmId = r.realmId where bd.typeId = @type and bd.locationId = @secLocation

I assumed the result returned by the first select would always be the first row of the final result set. (And the result from the second select would be the second row). In retrospect, it should have been obvious that this assumption was wrong. SQL never guarantees order unless the keyword *order by* is used!

To guarantee the order in a SQL union, an extra order column has to be used:

select bd.name, n.nodeId, r.realmName, 1 as nodeOrder from BuildDefaults bd join NetworkNodes n on bd.nodeId = n.nodeId join NetworkRealms r on cn.realmId = r.realmId where bd.typeId = @type and bd.locationId = @location union select bd.name, n.nodeId, r.realmName, 2 as nodeOrder from BuildDefaults bd join NetworkNodes n on bd.nodeId = n.nodeId join NetworkRealms r on n.realmId = r.realmId where bd.typeId = @type and bd.locationId = @secLocation order by nodeOrder