holyforce (2/1/2013)
SELECT *
FROM
(
SELECT CASE WHEN cus_no IN ('1','2')
AND product_type <> 'BIKE' THEN 1 ELSE 0 END AS the_column
FROM [aforementioned tables]
) subq
WHERE the_column = 1
Run just the inner subquery, it runs quick. Run the whole statement, it locks up again.
It's most likely not exactly right.
Make sure you've got the whole recorset in your Management Studio window before you say "subquery runs quick".
Because you do not have any filter every row is gonna be displayed, so SSMS starts displaying resulting rows as they arrive.
It does not need to wait till the whole data set is processed.
Different story when you apply the WHERE clause.
Because there is no way any index can be used SQL Server needs to build the recordset from the subquery as a table in memory, and then apply the filter against it.
_____________
Code for TallyGenerator