Refactored SQL projection?

Question

I don't like having the same thing defined in two places, if I can avoid it.

I realize the two queries below are dealing with two different tables, but those tables hold basically the same kind of data (distinct predicates warrant the two queries), and I think of the two projections below as "the same thing defined in two places".

When/if I modify these queries later, to include different columns, I'm sure I'll always want the projections to remain identical.

Given that, and without using dynamic SQL, and without '*' in any projection (not permitted in my production environment), can I define the "columnset" once and use it in both queries?

SELECT columnA
    , columnB
    , columnC
FROM Data

SELECT columnA
    , columnB
    , columnC
FROM DataArchive

Answer 1

Have your base be a union of Data and DataArchive and use an inline table-valued function (SQL Server 2005 and up)?

CREATE FUNCTION UnifiedData (@LiveOnly bit, @ArchiveOnly bit)
RETURNS TABLE
AS
RETURN (
    SELECT columnA
           ,columnB
           ,columnC
    FROM (
        SELECT 'Live' AS Src, * 
        FROM Data
        WHERE @ArchiveOnly = 0

        UNION ALL

        SELECT 'Archive' AS Src, *
        FROM DataArchive
        WHERE @LiveOnly = 0
    )
)

Not great, but should be handled pretty well by the optimizer since it's inlined.