Checksum in SQL Server

Dear Murugavel,

This is very interesting question.

The built-in CHECKUM function in SQL Server is built on a series of 4 bit left rotational xor operations. Since CHECKSUM function rotates the iterative checksum value 4 bits to the left (same thing as multiplying by 16 ). Before we do anything more, let's refresh our mind on how XOR works. 0 and 0 returns 0, 0 and 1 returns 1, 1 and 0 return 1 and finally 1 and 1 return 0.

Please see the below querys
select CHECKSUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1) --Here total 1 are 16
select CHECKSUM(0)
Out out of both is :0

Also
select CHECKSUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 )
select CHECKSUM(1 )
Out out of both is :1

Another is
select CHECKSUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 )
select CHECKSUM(1,1 )
Out out of both is : 17
Like wise..

You need to first understand how the Check-sum in SQL will work then you will get idea.

Happy coding.

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

If there is sum of values is EVEN inside the function
-- Returns Zero

SELECT CHECKSUM_AGG(CHECKSUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1))   --  sum of expressions(all values)  is even ( here 16 times same value(1) occurred)

-- Returns non-zero ( Sum of numbers is ODD)

SELECT CHECKSUM_AGG(CHECKSUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2)) 

Refer this link
http://stackoverflow.com/questions/11994430/what-conditions-cause-checksum-agg-to-return-0

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Hi Allemahesh,

Nice Explanation.

But i want to compare two tables using checksum with 16 columns means it always returns zero if there is change in the table.

Then how can i avoid this

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Dear Murugavel,

I am litter confused with you question. Can you post some data or tables data, so that I can help you.

Happy Coding.

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

SELECT CHECKSUM_AGG(CHECKSUM(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)) as CheckSum0 -- returns 0 source table

SELECT CHECKSUM_AGG(CHECKSUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)) as CheckSum1 -- returns 0 compare table

But the data is difference.....

Hope u clear now

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Why do you want to compare two tables with checksum?
Can you elaborate your requirement.. We will surely provide you other solution...

For rows comparison there are more than 3 approaches...
1) EXCEPT operator

SELECT columns-list FROM table1

EXCEPT

SELECT columns-list FROM table1

2) LEFT JOIN
and so on

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Dear Murgugavel,

You are right. Pleas see the how XOR works:-
0 and 0 = 0,
0 and 1 = 1,
1 and 0 = 1 and finally
1 and 1 = 0.

This status that if you compare check of
SELECT CHECKSUM_AGG(CHECKSUM(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)) as CheckSum0 -- returns 0 source table
SELECT CHECKSUM_AGG(CHECKSUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)) as CheckSum1 -- returns 0 compare table
It will return allows 0.

Also here you not comparing the two tables you are calculating the check of two different tables.
Hope you clear this.

Happy coding.

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

Sorry I can't get it

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

DECLARE @Query1Checksum bigint

DECLARE @Query2Checksum bigint

Select @Query1Checksum = CHECKSUM_AGG(CHECKSUM(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0))

Select @Query2Checksum = CHECKSUM_AGG(CHECKSUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1))



IF @Query1Checksum = @Query2Checksum

PRINT 'Equal'

ELSE

PRINT 'NOT Equal'

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Hi Murugavel,
>> i want to compare two tables using checksum with 16 columns
I'm not clear about the requirement...
Can you give some sample data and what u supposed to get the output (with explanation)?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Hi Bandi,

Please see my previous respose....

o/p:
Not equal (because data different)

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Dear Murugavel,

First I have see the you code,
I want to tell you that you are comparing only check sum and not the tables.
So the check of two will be the same. That why you are getting the Equal.
For example:-
2 + 2 = 4 and
1 + 3 = 4
Like wise
CHECKSUM_AGG(CHECKSUM(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)) = 0 and
CHECKSUM_AGG(CHECKSUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)) = 0

Also you said, Not equal (because data different) that means check of any data will same in certain condition
Like
select CHECKSUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1) = 0
select CHECKSUM(0) = 0
But here you data is different but check is the same.
This means that in check of two different data will be come same.

Hope you got it.

Happy coding.

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Refer this link once..
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/240127c4-bb33-40ac-ad92-cc316896a871/comparing-2-tables-via-checksum
How you will give 0's or 1's instead of column names for two tables comparison.......
Its better to post sample data so that only we will provide solution...
By looking into your previous post I haven't understand the problem

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

--sample example to compare two tables based on one column

create table #t (id int, a varchar(20))

create table #t1 (id int, a varchar(20))

INSERT INTO #t VALUES(1,'A'),(2,'B'),(3,'C')

INSERT INTO #t1 VALUES(1,'a1'),(2,'B'),(3,'c')

select * from #t t

inner join #t1 t1 on t.id=t1.id

where BINARY_CHECKSUM(t.a)<>BINARY_CHECKSUM(t1.a)



DROP TABLE #t

DROP TABLE #t1

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

In this case, every multiples of 16 are always equal. But it is not correct rite?

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Dear Murugavel,
Yes you are right,
See the below one
select CHECKSUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1) = 0
select CHECKSUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1) = 0
select CHECKSUM(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1) = 0

Hope you understand.

happy coding.

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Have you solved out the issue?
If yes, mark this thread as answered...
If you worked out with alternative solution please share the solution here so that you can help others....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

hi
Murugavel

refer this

http://www.mssqltips.com/sqlservertip/1023/checksum-functions-in-sql-server-2005/
http://www.sqllike.com/basics-of-working-with-checksu.html

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator