curious_sqldba (5/15/2013)

---

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