Hi,
I'm wondering about best index strategy and the way to express a query for the following situation: I have two columns in the source and the target tables. When one column is populated with the value, the other is NULL. Columns are of bigint type. There is also a tinyint column with 1 for the first column populated and 2 for the second column populated.
So I expressed the JOIN condition in the MERGE command this way:
ON ISNULL(srce.FirstColumn, srce.SecondColumn) = ISNULL(trgt.FirstColumn, trgt.SecondColumn) and trgt.Indicator = srce.Indicator
I also created two filtered indexes for the source table (which is a temp table #Source)
index on FirstColumn where Indicator = 1 and similar for the second column.
Do you think it's the correct approach or what would you suggest for the problem in question (we're not going to change the table design).
Thanks a lot in advance.