Eric M Russell (5/15/2013)
WHERE A.Pid = 9
AND ( Isnull(A.ID, 0) <> Isnull(V.ID, 0)
OR Isnull(A.FacilityPatientID, '') <> Isnull(V.FacilityPatientID, '')
OR Isnull(A.FirstName, '') <> Isnull(V.FirstName, '')
OR Isnull(A.MiddleInitial, '') <> Isnull(V.MiddleInitial, '')
OR Isnull(A.LastName, '') <> Isnull(V.LastName, '')
OR Isnull(A.SSN, '') <> Isnull(V.SSN, '') . . .
. . .
. . .
. . .
If you are comparing two tables with similar columns, something like a transactional table versus a staging table, then perhaps you can add to both tables a persisted computed column derived from a CHECKSUM of the column list. Once done, add index on PID + the checksum column. If my assumption is correct, the join operation could potentially be covered by this 2 column index, which would be very efficient.
create table MYTable
(
pid int not null,
a int null, b int null, c int null,
xupdate as checksum( a, b, c) persisted
);
create index ix_pid_xupdate on MYTable ( pid, xupdate );
Thanks. Few questions before i try:
i) in reality i have about 20 columns in my table, so for this new computer column would i do like checksum( col1,col2...col 20)?
ii) What changes do i need to make in my update statement?
Thanks