curious_sqldba (5/16/2013)
Eric M Russell (5/16/2013)
curious_sqldba (5/16/2013)
Ok makes sense, i am joining on a table and a view, i might have to add this column on all the underlying tables...
I don't know what lay beneath your view, but indexed hash columns are an efficient alternative to performing non-equal joins on non-key and non-indexed columns. It's useful in a scenario where you need to know if at least one of X columns differ, but you arn't concerned with which specific column.
If i join a view and a table, do i need to have that computed columns on all the tables used in the view?
Now it sounds as if the table you're updating, what you're calling MyTable in the provided example, is a denormalized and it's columset is derived from multiple other tables joined behind the scenes in a view.
If that's the case, then you'll need to add a column computed on checksum() on MyTable, and then index it on ID and checksum. You'll also need to add the same computed column on the view, but that one can't be persisted or indexable. Still it will be an improvement over what you're doing now comparing 20+ columns against each other.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho