Jonathan Marshall (11/12/2013)
The queries are updating lookup tables that do not have a value by using a subquery to the lookup table.
I like that idea of using a computed column as these will be scanning through lots of data.
WHERE [column1] + [column2] NOT IN
(SELECT [column3] + [column4] from dbo.table order by [column1] + [column2])
Now I understand why you're wanting concatenate columns in WHERE clause.
First, I don't understand why you have an ORDER BY clause in that sub-select. I'm surprised that would even parse.
When I have a similar situation to select where two or more columns in one set don't intersect with two or more columns in another set, I typically do an outer join. You can experiment to see which performs better.
left join TableB on column1 = column3 and column2 = column4
where column3 is null;
Also, if basically all you need is to return column1 and column2 from one set where not contained in column3 and column4 of another set, then you can use the EXCEPT clause. It's essentially the reverse of UNION clause, because you're taking 2nd set away from 1st set rather than unionizing them.
SELECT column1, column2 from TableA
SELECT column3, column4 from TableB;
If these sets will be large, then you'll probably benefit from indexing (column1, column2) and (column3, column4).
"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."