GilaMonster (11/13/2013)
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])
WHERE NOT EXISTS (SELECT 1 FROM dbo.table t WHERE t.[column3] = [column1] and t.[column4] = [column2])
By guessing there's a reason for performing the concatenation before the comparison I don't think this solution would provide the same result as the original query.
Example: [column1] ='ab', [column2] ='cd', [column3] ='a' and [column4] ='bcd'
The original query would exclude this row since [column1] + [column2] = [column3] + [column4]) . Your query would consider the comparison not being equal.
The question is: What business logic is intended?