SQL Query for finding a unique Item in one of many tables

Question

I have the need to search for an item that exists in one of many (~20) tables I can safely assume that the item only exists in one of the tables, all tables contains an index with the same column name and value type.

Tables vary in on of their columns, containing one of a few possible value types (int, string, binary. etc.)

What would be the most efficient way to search for the item, of a few items that match a criteria? Please note that there is NO NEED for any join, as item is uniquely stored in one row in one table.

Amit

Answer 1

SQL UNION with the 20+ tables enumerated in the UNION. You could set this up as a view then use the view for selection.

For the single column that varies you could convert all the types to a string for the view.

For an example:

CREATE VIEW Combined_Table AS
SELECT keyfield, Cast(Integerb as varchar) as varcharfield
  FROM table1
UNION
SELECT keyfield, varcharfield
  FROM table2
UNION ...

then you can use the view as a table:

SELECT keyfield, varcharfield
  FROM Combined_table
 WHERE keyfield = 'key'