LutzM (11/13/2013)
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.
I wouldn't be so sure. I've seen 'concatenate and IN' very often, done that way because IN is what the developer knows and concatenate is the only way he can figure out to do an IN on two columns.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability