scalar valued function having cte and top 1 within 'Exist' function

Question

My query uses 'exists' function as filter and it has scalar valued function within it. The scalar valued funciton contains cte and "(select top 1 1)". When I use exists it does not filter at all. Rather when I use "where 1=(svf)" it seems work.Did I miss anything or anything wrong in exists query?

SELECT * FROM TBL1
WHERE EXISTS (SELECT SVF(1,2))
--where SVF is my scalar valued from which returns bit and looks like as shown below.


CREATE FUNCTION SVF
(
    @x int,
    @y int
)
RETURNS bit
AS
BEGIN
declare @result bit
;WITH T1
    AS (
        SELECT * from tbl2
    )
SELECT  @result= (select top 1 1 
        FROM t1
        )
        return isnull(@result,0)
END
GO

--the following query works
SELECT * FROM TBL1
WHERE 1=(SELECT SVF(1,2))

Answer 1

The EXISTS() operator

Returns TRUE if a subquery contains any rows.

(emphasis added)

That's important because your Scalar function is going to return a value, either 1 or 0 (as Damien pointed out even NULL will satisfy) but IT IS going to return a value.

And because it returns a value, the EXISTS is always going to return TRUE.

You are basically asking SQL Server:

-- Doesn't matter what my function returns as it's always going to return a row...
BEGIN 
-- then run this query
SELECT * FROM TBL1;
END;

Try to re-write your query to not use the function as a Scalar function is not normally Set Based and so is normally a performance killer.

Answer 2

As it already been stated, the exists function will always evaluate as true, since your scalar function returns a value. Exists would only work when the function call could eventually return no value.

You could use cross apply for example:

SELECT * 
FROM TBL1 as T1
CROSS APPLY (SELECT svf = dbo.SVF(T1.x,T2.y)) c
WHERE svf = 1

EDIT: Keep in mind that scalar functions are most of the times performance killers, as those are not sargable.