Combine tables only if no results We have a table FOO that has a huge amount of data in it, most of which is only used infrequently and by (slow) user-interaction.

We would like to move all data that is more than 12 months old to an archive table, FOO_ARCHIVE. That will make the frequent reads and writes into FOO fast enough.

There are many complex stored procedures in our application(s) that currently query against FOO, so we need a way to sensibly combine the two tables when reporting.

I have considered using a union, but I'd rather only look in the archive table if I need to, which is when nothing is in the live table.

SELECT Field1, Field2, Field3, FieldN FROM FOO
UNION ALL
SELECT Field1, Field2, Field3, FieldN FROM FOO_ARCHIVE


However, I'd rather only hit archive if I have to, so perhaps create a stored procedure for this:

SELECT Field1, Field2, Field3, FieldN FROM FOO
IF @@ROWCOUNT = 0
BEGIN
    SELECT Field1, Field2, Field3, FieldN FROM FOO_ARCHIVE
END


But I only want one result set returned, and there can be many conditions applied to the query.

The solution must be supportable on SQL Server 2005, due to client deployments.
You can't say "UNION ALL ONLY IF SOME CONDITION" but you could do:

IF EXISTS (SELECT 1 FROM dbo.FOO)
BEGIN
  SELECT Column1, Column2, ColumnN FROM dbo.FOO;
END
ELSE
BEGIN
  SELECT Column1, Column2, Column3 FROM dbo.FOO_ARCHIVE;
END