CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
sree
sree

Reputation: 1960

Generate view sql dynamically

I have a view with code as follows

create view v1
as
section1 --I get column1 data here
union all
section2 --I get column2 data here
union all
section3 --I get column3 data here

Now, this view is used in multiple places and will be joined on column1 or 2 or 3 depending upon the place where we use this like below.

select * from tabl1 t1 join v1 on t1.column1 = v1.column1
select * from tabl1 t2 join v1 on t2.column2 = v1.column2

etc

But if it is joining on column1, computation for column2,3 i.e, section 2,3 is not needed. As per current business rules, we cant split the view into multiple views. Now, what I need is that if view gets joined on column1, section2,3 should not be computed and similar is case with column2,section1,3 and column3,section1,2

Could someone please help me how to achieve this

Thanks, Sree

Upvotes: 1

Views: 67

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221370

In order to hint the optimiser that it does not need to generate any rows (and those not do any computation) in a specific union subquery, you have to tell it that you don't actually need the info. In some cases, it would be sufficient to just not SELECT * (i.e. not select v1.column2 and v1.column3):

select t1.*, v1.column1 from tabl1 t1 join v1 on t1.column1 = v1.column1
select t1.*, v1.column2 from tabl1 t2 join v1 on t2.column2 = v1.column2

There may be edge cases where the optimiser still cannot prove that the calculation is unneeded, depending on your actual view. In those cases, it may help to have a constant column that clearly discriminates each subquery:

create view v1
as
select 'section1' AS discriminator
-- rest of section1
union all
select 'section2' AS discriminator
-- rest of section2
union all
select 'section3' AS discriminator
-- rest of section3

And now, use a constant in your query as well (not a bind variable) to select the discriminator:

select t1.*, v1.column1 
from tabl1 t1 join v1 on t1.column1 = v1.column1 
where discriminator = 'section1'

select t1.*, v1.column2 
from tabl1 t2 join v1 on t2.column2 = v1.column2
where discriminator = 'section2'

Of course, at this point, one may wonder whether you perhaps shouldn't create 3 distinct views and select from them directly, but I don't know your requirements...

Upvotes: 3

Related Questions