SQL Server vs. EXISTS

Using Microsoft SQL Server, I had a query that retrieved rows from a view of some complexity, i.e. something like this:

SELECT * FROM vwComplex WHERE ...

This query ran basically instantaneously, even though the view involved numerous UNION ALLs and joins on coalesced keys. My sample query criteria selected ~100 out of the view's ~80,000 rows.

In the general case, I needed a query that returned a single bit indicating whether the specific criteria will select at least one row. I tried this:

SELECT
  CASE WHEN EXISTS (
    SELECT * FROM vwComplex WHERE ...
  ) THEN 1 ELSE 0 END

I presumed that this statement would run faster than the original unadorned query since it can stop executing as soon as it finds the first row. My presumption was incorrect -- the query ran for a long, long time.

In this case, at least, it turns out that it is faster to run the full query than to perform an existence check:

SELECT SIGN(COUNT(*)) FROM vwComplex WHERE ...

No surprises here, of course. SQL query plan generation remains a mystical exercise.