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

I can't think of any efficient way of doing so. You could of course make a view with a UNION ALL of the two tables with the addition of a column that holds the table name as a string, then do SELECT columnA, columnB, columnC FROM view WHERE table = 'Data' but that feels like a rather ugly hack.

Answer 2

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.

Answer 3

Yeah, SQL is awful this way. There is no all-around satisfactory way to do it. Here's an example using synonyms:

if object_id('DataSynonym') is not null drop synonym DataSynonym
create synonym DataSynonym for Data
select columnA, columnB, columnC from DataSynonym

if object_id('DataSynonym') is not null drop synonym DataSynonym
create synonym DataSynonym for DataArchive
select columnA, columnB, columnC from DataSynonym

The issue with using synonyms this way is that they are global in scope. If you redefine a synonym on the fly in one connection, it will affect users on all other connections.

Temporary views/functions or view/function variables would go a long way to fix the problem. Or a macro language like what's available in SAS.

Answer 4

Create a view on Data UNION ALL DataArchive. If you are later able to to refactor and combine the 2 tables, this will be transparent to your existing queries.