How to calculate the hash of a row in SQL Anywhere 11 database table?

Question

My application is continuously polling the database. For optimization purpose, I want the application to query the database only if the tables have been modified. So I want to calculate the HASH of entire table and compare it with the last-saved-hash of table. (I plan to compute the hash by first calculating HASH of each row and then followed by their hash i.e. HASH of HASHes)

I found that there is Checksum() sql utility function for SQL Server which computes HASH/Checksum for one row.

Is there any similar utility/query to find the HASH of a row in SQL Anywhere 11 database?

FYI, the database table does not have any coloumn with the precomputed HASH/Checksum.

Answer 1

Got the answer. We can compute the hash on a particular column of a table using below query:

-- SELECT HASH(coulum_name, hash_algorithm) 
-- For example:
SELECT HASH(coulmn, 'md5') 
FROM MyTable

Answer 2

This creates a hash over all data of a table, to detect changes in any column:

CREATE VARIABLE @tabledata LONG VARCHAR;
UNLOAD TABLE MyTable INTO VARIABLE @tabledata ORDER ON QUOTES OFF COMPRESSED;
SET @tabledata = Hash(@tabledata);
IF (@tabledata <> '40407ede9683bcfb46bc25151139f62c') THEN
    SELECT @tabledata AS hash;
    SELECT * FROM MyTable;
ENDIF;
DROP VARIABLE @tabledata;

Of course this is expensive and shouldn't be used if the data is hundreds of megabytes. But if the only other way is comparing all the data for any changes, this will be faster and produces load and memory consumption only on the db server.

If the change detection is only needed for a few columns, you can use UNLOAD SELECT col FROM table INTO ... instead.