how checksum works?

Question

Post reply

how checksum works?

892717952

SSC Eights!

Points: 843
More actions
April 18, 2021 at 4:10 am

#3872591
1. Could you please me how checksum work in the SQL statement ? below is an example.
SELECT * FROM Tablename1 WHERE CHECKSUM(*)
NOT IN (SELECT CHECKSUM(*) FROM TableName2)
2. and there is page checksum and database backup checksum, and what does it do while page checksum and database backup checksum?
thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

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

DesNorton SSC-Insane Points: 24386 More actions · Answer 1

For the above to work, TableName1 and TableName2 would need to have have the same structure.

Since the structure is the same, why not use

SELECT * FROM TableName1
EXCEPT
SELECT * FROM TableName2

How to post data/code on a forum to get the best help.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

892717952 wrote:

Could you please me how checksum work in the SQL statement ? below is an example.
SELECT * FROM Tablename1 WHERE CHECKSUM(*)
NOT IN (SELECT CHECKSUM(*) FROM TableName2)
2. and there is page checksum and database backup checksum, and what does it do while page checksum and database backup checksum?
thanks!

There is no way that I'd use CHECKSUM() for any such comparison. The risk of collisions is just too high and you can verify that even Microsoft says so by doing a search for CHECKSUM in SQL Server (which you should have done already but probably didn't do or you'd have already know this). The document also makes a suggestion as to a better alternative.

Remember also that as small as the possibilities of a collision get with the larger HASHBYTES algorithms, there is no guarantee of there not being collisions.

What that means is that if you have an inequality, then they're guaranteed to be different. If you have an equality, then they are NOT guaranteed to be the same and you would need a secondary check for such supposed equal values.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

892717952 SSC Eights! Points: 843 More actions · Answer 3

DesNorton wrote:

For the above to work, TableName1 and TableName2 would need to have have the same structure.
Since the structure is the same, why not use
SELECT * FROM TableName1
EXCEPT
SELECT * FROM TableName2

thank you DesNorton!

892717952 SSC Eights! Points: 843 More actions · Answer 4

thank you Jeff Moden!

I want to the theory of checksum while using it as comparison condition.

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 5

Here's a bit of theory: https://www.online-tech-tips.com/cool-websites/what-is-checksum/

HASHBYTES can use SHA1.

This is a quick check, but not necessarily an absolute one.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

EXCEPT is great. But to be clear, it doesn't require that the tables have identical structures, it compares the results of two queries. You can select a subset of columns, combine columns from joined tables, and even do datatype conversions (like int to varchar) in the queries. Also, you can make the EXCEPT run much faster if you have a common key to join on for your second query.

declare @Tbl1 table (RowID int primary key, Color varchar(30), ColorCode varchar(5))
declare @Tbl2 table (RowID int primary key, Color varchar(30), ColorNumber int)

insert into @Tbl1 
values (1,'Red','30'), (2,'Blue','35'), (3,'Yellow', '40'), (4,'Paisley','ABC')

insert into @Tbl2
values (1,'Red',30), (2,'Blue',35), (3,'Yellow', 45)   -- No match because 45 != 40

select RowID, Color, ColorCode
from @tbl1

except 

select t1.RowID, t2.Color, convert(varchar(5), t2.ColorNumber)
from @Tbl1 t1
join @Tbl2 t2 on t1.RowID = t2.RowID

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

892717952 SSC Eights! Points: 843 More actions · Answer 7

The Dixie Flatline wrote:

EXCEPT is great. But to be clear, it doesn't require that the tables have identical structures, it compares the results of two queries. You can select a subset of columns, combine columns from joined tables, and even do datatype conversions (like int to varchar) in the queries. Also, you can make the EXCEPT run much faster if you have a common key to join on for your second query.
declare @Tbl1 table (RowID int primary key, Color varchar(30), ColorCode varchar(5))
declare @Tbl2 table (RowID int primary key, Color varchar(30), ColorNumber int)

insert into @Tbl1 
values (1,'Red','30'), (2,'Blue','35'), (3,'Yellow', '40'), (4,'Paisley','ABC')

insert into @Tbl2
values (1,'Red',30), (2,'Blue',35), (3,'Yellow', 45)   -- No match because 45 != 40

select RowID, Color, ColorCode
from @tbl1

except 

select t1.RowID, t2.Color, convert(varchar(5), t2.ColorNumber)
from @Tbl1 t1
join @Tbl2 t2 on t1.RowID = t2.RowID

Thank you for your kind help!