Refactored SQL projection?
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
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.
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.
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.
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.