binary check sum on nvarchar(max)

Question

binary check sum on nvarchar(max)

ramuvanparti

SSCrazy

Points: 2368
More actions
October 15, 2009 at 7:35 am

#216360

hi all,
my table has nvarchar(max) in table and same column in other table. i want to compare records of two tables using binary_checkSum function but as it nvarchar(max) it is executing very slow can one tell me how to optimize the same
Regards
Ramu V

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Running a binary checksum on large volumes of data is going to be slow. More powerful hardware is the only thing I can think of that would realistically speed it up.

I have to ask, why would you want to do this? What's the business-reason behind it? There might be better solutions than checksums.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ramuvanparti SSCrazy Points: 2368 More actions · Answer 2

Actually i have dataware house table in we have transaction table and history table, after a regular interval we compare transaction table and history table for any update insert and delete operation. hence we have used binary check sum to check the changes in the tables.....

Please suggest any alternative which solve the issue....

GSquared (10/15/2009)
Running a binary checksum on large volumes of data is going to be slow. More powerful hardware is the only thing I can think of that would realistically speed it up.
I have to ask, why would you want to do this? What's the business-reason behind it? There might be better solutions than checksums.

GSquared SSC Guru Points: 260824 More actions · Answer 3

I would suggest using a rowversion column for that. Look up "timestamp" in Books Online, since that explains how to use these. It's a very, very easy way to tell if a row has changed.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Paul White SSC Guru Points: 150457 More actions · Answer 4

If you are unable to add a rowversion column to the base tables, you could keep track of changed data using a trigger to record the primary keys of modified rows. Once you upgrade to 2008, you could also take a look at Change Data Capture and Change Tracking.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi