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

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 tab开发者_Python百科le 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

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?

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.

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