# Checksum in SQL Server

Posted by Murugavelmsc under Sql Server on 8/28/2013 | Points: 10 | Views : 2424 | Status : [Member] | Replies : 18
Hi Expert,

why it returns 0?

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

Regards,
Murugavel S
http://murugavelmsc.blogspot.in/

#### Responses

Posted by: Allemahesh on: 8/28/2013 [Member] [MVP] Silver | Points: 25
 0 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
Posted by: Bandi on: 8/28/2013 [Member] [MVP] Platinum | Points: 25
 0 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.gifMurugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Murugavelmsc on: 8/28/2013 [Member] Starter | Points: 25
 0 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
Posted by: Allemahesh on: 8/28/2013 [Member] [MVP] Silver | Points: 25
 0 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
Posted by: Murugavelmsc on: 8/28/2013 [Member] Starter | Points: 25
 0 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 nowRegards, Murugavel S murugavel.sadagopan@gmail.com http://murugavelmsc.blogspot.in/Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/28/2013 [Member] [MVP] Platinum | Points: 25
 0 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 onMark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gifMurugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Allemahesh on: 8/28/2013 [Member] [MVP] Silver | Points: 25
 0 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
Posted by: Murugavelmsc on: 8/28/2013 [Member] Starter | Points: 25
Posted by: Murugavelmsc on: 8/28/2013 [Member] Starter | Points: 25
 0 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
Posted by: Bandi on: 8/28/2013 [Member] [MVP] Platinum | Points: 25
 0 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.gifMurugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Murugavelmsc on: 8/28/2013 [Member] Starter | Points: 25
Posted by: Allemahesh on: 8/28/2013 [Member] [MVP] Silver | Points: 25
 0 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
Posted by: Bandi on: 8/28/2013 [Member] [MVP] Platinum | Points: 25
 0 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 problemMark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gifMurugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 8/28/2013 [Member] [MVP] Platinum | Points: 25
 0 --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.gifMurugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Murugavelmsc on: 8/28/2013 [Member] Starter | Points: 25
 0 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
Posted by: Allemahesh on: 8/28/2013 [Member] [MVP] Silver | Points: 25
 0 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
Posted by: Bandi on: 8/30/2013 [Member] [MVP] Platinum | Points: 25
 0 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.gifMurugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jayakumars on: 8/30/2013 [Member] [MVP] Bronze | Points: 25