SQL - Is it better to do a subquery here or an extended WHERE clause?

Question

The CUST table below will be joined with ~10 tables.

For this subquery in particular, am I better off simply joining up directly with the Customer table and moving the subquery's 4-part WHERE clause to the main query's WHERE clause?

I'm primarily wondering if it is possible to cut down on the amount of processing that SQL Server has to do if we localize portions of the master WHERE Clause by creating subqueries as below.

select * From 
(select CKey, CID, CName from MainDB.dbo.Customer 
     where 
        LOC = 'ARK' 
        and Status = 1 
        and CID not like 'KAN%' 
        and CID not like 'MIS%') as CUST

Answer 1

Based on what you provided, there's no need for the subquery. Without the ~10 joins to the derived table CUST, it's extremely difficult to say what should or should not be done.

Answer 2

In older versions, yes, I've seen huge improvements using derived tables (not a subquery) rather then all in one JOIN/WHERE. It's less relevant now since SQL Server 2005

However, why not try both and see what happens?

Answer 3

I suggest that if you are joining that many tables it would be better to build a view.