CODEWITHSUNDEEP
sql-server
Mohit Dagar
Mohit Dagar

Reputation: 570

Select from either of two tables

I have 2 tables.

Lets call them Table1 and Table2. Both tables have the same structure and data type.

Table1 is a transaction table and Table2 is a historical data table.

I need to do something like this:

Select * from case  when 'transnational' then Table1 else Table2 end

I do not want to use 2 select statement to do this however.

Can I use the CASE statement for this?

Upvotes: 5

Views: 2218

Answers (1)

Y.B.
Y.B.

Reputation: 3586

Great question! Let's try:

Create Table tab0 (col int primary key);
Create Table tab1 (col int primary key);

Insert Into tab0 (col) Values (0);
Insert Into tab1 (col) Values (1);
GO

Minimalist Union All solution would be:

Declare @tabindex bit = 0;
Select * From tab0 Where @tabindex = 1 Union All
Select * From tab1 Where @tabindex = 0;

Execution plan indicate two table scans and filters with one concatenation.

That is not good enough. Let's see whether optimizer can actually skip table processing if conditions are not right:

Select * From tab0 Where 1 = 1 Union All
Select * From tab1 Where 1 = 0
GO

... and Execution plan indicate one table scan and one Compute Scalar at cost 0%. So, technically optimizer can do it. The problem is any condition involving variable would get evaluated against the table resulting in a table scan. So we just need to evaluate the condition before Select. Well, there is an answer to that too by the name of table-valued function:

Create Function tab (@tabNo int)
Returns @RetTab Table (col int)
As
Begin
  If @tabNo = 1 Insert Into @RetTab Select * From tab1
  Else          Insert Into @RetTab Select * From tab0;
  Return;
End;

Now if we run Select * From tab(1); or Select * From tab(0); Execution plan would indicate just one table scan, one Table Valued Function and one Sequence both costing 0%.

So the answer is yes, we can use CASE statement in table-valued function.

Update following Shnugo comment. The function can actually be improved:

Create Function tab (@tabNo int)
Returns Table
As
  Return
    Select * From tab0 Where @tabNo = 0 Union All
    Select * From tab1 Where @tabNo = 1;
GO

Now Select * From tab(1); or Select * From tab(0); results in one Table Scan and one Compute Scalar only. Apparently with compiled function the optimizer know not to look for the parameter variable in the table.

Upvotes: 2

Related Questions