how can we minus two sets in SQLServer. e.g.
select * from table1
select * from table2
Right, no minus. I would recommend using JOIN like this:
SELECT *FROM Table1 aLEFT JOIN Table2 b ON a.Col1 = b.Col1 AND a.Col2 = b.Col2WHERE b.Col1 IS NULL
I think previous example would cause SQL server to run multiple queries against data, while this one simply joins tables and extracts rows that exist in table B.
If you check Kalen Delaney's Inside SQL Server 2000, Chapter 7, Corrolated Subqueries, Page 354, she explains that using the LEFT JOIN and testing for null uses less than half the logical I/O's of the corrolated subquery. Usually a join is more efficient than the alternative.
You may have indexes that could effect the outcome.
If you use alot of corrolated subqueries you may have to work on thinking more set based, and less row-by-row.