WAT - @@Rowcount, SELECT, DISTINCT, CTEs, and unexpected results.

 I was looking at a piece of code that was slow. The slow part was due to a "where (a.fielda = @param or a.fieldb = @param)". So I took it from 

declare @field varchar(10) = (select distinct (field) from myview a where (a.fielda = @param or a.fieldb = @param) and a.fieldc = @param2)

if @@rowcount > 0

BEGIN

blahblahblah

END

to

declare @field varchar(10);

with cte_distincter as 

(

select fielda from myview a where (a.fielda = @param) and a.fieldc = @param2

UNION ALL

select fielda from myview a where (a.fieldb = @param) and a.fieldc = @param2

)select distinct @field = fielda from cte_distincter

if @@rowcount >0

BEGIN

blahblahblah

END

Pretty good, right? 150-250x faster. Problem solved.

Except.. now the @@rowcount code doesn't fire right. Because the first code, event if it returns an empty set, has a @@rowcount of 1. The CTE code will have the @@rowcount = 0 if it's NULL. So, had to drop the CTE, and just went to using a derived table. Which works. But jeeze, talk about unexpected behavior. CTEs aren't always the solution!

declare @field varchar(10) = (select DISTINCT(fielda) from 

(

select fielda from myview a where (a.fielda = @param) and a.fieldc = @param2

UNION ALL

select fielda from myview a where (a.fieldb = @param) and a.fieldc = @param2

) a)