Aggregate set of sql results in a value of HashBytes

Question

Is there an equivalent

CHECKSUM_AGG(CHECKSUM(*)) 

for hashbytes?

I know what you can do

 SELECT HashBytes('MD5', CONVERT(VARCHAR,Field1) + '|' + CONVERT(VARCHAR,Field2) + '|' + CONVERT(VARCHAR,field3) + '|' ) FROM MyTable 

But I'm not sure how to combine all calculated hash byte entries into a single value inside SQL.

One of the reasons I would like to do this is to determine if the data in the source table has changed from the previous download file before moving the data to my system.

Answer 1

If you want to check if a given row has changed, I highly recommend that you use the "timestamp" column. The value is automatically updated by the Sql server in each row modification. Then, if the row is changed, the value will be different after modification, and you could notice it without implementing logic or querying the entire table.

But if you want to know if at least one row is updated, I recommend that you use:

DECLARE @Tablename sysname = 'MyTable';

SELECT modify_date FROM sys.tables WHERE name = @Tablename;

(If you use .Net in your business layer, you might be interested in taking a look at SqlDependency)

Answer 2

You can iterate over all records and combine hashes into one

 declare @c cursor; declare @data varchar(max); declare @hash varchar(400) = ''; set @c = cursor fast_forward for select cast(SomeINTData as varchar(50)) + SomeTextData from TFact where Year = @year and Month = @month; open @c fetch next from @c into @data while @@FETCH_STATUS = 0 begin set @hash = HASHBYTES('sha1', @hash + @data) fetch next from @c into @data end select @hash Ha