aaron.reese (9/10/2012)
the view with UNION ALL the same query once for each agent table
Not something I recommend. The view will have to be recreated every time a new agents table is added, and it can break if the schema on any of the underlying tables is altered (except if WITH SCHEMABINDING is used, and that introduces its own issues).
Plus the more UNIONs get added to a query, the worse the performance becomes. It gets really messy really quickly.