Is select case when null better than iif(exists)? I try to reduce huge performance issues on our application database. We use SQL Server 2014 and we have a lot of views.  

I wonder if there is a significant impact of rewriting left joins to use exists in projections ?

My question is: is there any best practices concerning performance when constructing queries that project different data depending on related matches existence ?

I mean,
which of these queries could be considered better ?

1) :

select
        a.Id,
        case when b.Id is not null 
             then convert(bit,1) 
             else convert(bit,0)
        end IsActive
from A a
left join B b on a.Id = b.Id;


2) :

select
        a.Id,
        iif(exists(select top 1 1 from B where Id = a.Id),
            convert(bit,1), 
            convert(bit,0)
           )  IsActive
from A a;


By the way, if you have any comments on the form of the queries, or general advice, please feel free to comment.
First of all, the two queries are only equivalent if and only if there is a UNIQUE or primary key constraint on B (id). If there isn't, there is no point in using one instead of the other or in comparing their efficiency.

Now, assuming there is such a constraint, the question is confused about CASE and EXISTS. The IIF vs CASE choice is orthogonal to the LEFT JOIN vs EXISTS subquery choice. Meaning, we could easily rewrite the first query with IIF() and the second with CASE. See:

(by the way, I removed the TOP 1 as it is redundant in an EXISTS subquery)

-- query 2a
select
        a.Id,
        case when exists (select 1 from B where Id = a.Id)
             then convert(bit,1) 
             else convert(bit,0)
        end IsActive
from A a ;


There is also a 3rd way to rewrite the query, similar to the 2nd query, using a correlated subquery but without the EXISTS. This could also be written with either CASE or IIF() but I'm writing only the case here:

-- query 3
select
        a.Id,
        case when (select B.Id from B where B.Id = a.Id) is not null 
             then convert(bit,1) 
             else convert(bit,0)
        end IsActive
from A a ;


So we have 3 ways to write the query (and 6 if we count the IIF versions) and there are possibly more ways. Using OUTER APPLY comes to mind so lets have a 4th way:

-- query 4
select
        a.Id,
        case when b.Id is not null 
             then convert(bit,1) 
             else convert(bit,0)
        end IsActive
from A a 
  outer apply (select B.Id from B where B.Id = a.Id) b ;




The real question is of course which is more efficient. Regarding CASE vs IIF() there is probably no difference at all and both ways produce identical plans in all 4 cases. IIF() was added in recent versions of SQL Server but can't do anything that wasn't already possible with CASE. So I can't find any real use of it, unless to make a query friendly to developers coming from MS-Access.

For the 4 different ways though, there is no better answer than testing it yourself! In your environment, with your tables/views, your data and sizes, your hardware, your settings, etc. All these may matter in one way or another. 

If I were to blindly guess without testing, I'd choose the LEFT JOIN way, since correlated subqueries (like the EXISTS or the 3rd subquery) may not always yield the best plan. The optimizer is pretty clever at understanding that all the queries would be equivalent but that also depends on the complexity of the queries. If A and B are base tables, ok. But if they are complex views over complex views over ..., then it may not.

And if you plan to define this query as a view and then use it in queries like this:

select * 
from my_view
where IsActive = convert(bit, 0) ;


you might fall into performance problems. Hiding the values into computed columns inside views will prohibit the optimizer from using indexes on the underlying tables.