September 14, 2015 at 9:56 pm
Comments posted to this topic are about the item The checksum
September 14, 2015 at 10:59 pm
he he, how to tell when?
😎
September 15, 2015 at 1:42 am
This makes no sense to me. If the checksum is calculated differently, then when the checksums match that will most likely imply the data is different, not the same, so a round-trip will be needed.
Or maybe I'm missing something, in which can anyone explain this?
September 15, 2015 at 2:01 am
robin 66943 (9/15/2015)
This makes no sense to me. If the checksum is calculated differently, then when the checksums match that will most likely imply the data is different, not the same, so a round-trip will be needed.Or maybe I'm missing something, in which can anyone explain this?
Don't think you are missing anything here! Without detailed knowledge of column order and column values in the checksum calculation, no assumption can be made on equality of the values, hence one has to do the round-trip anyway to check those, ergo the statement is false.
😎
September 15, 2015 at 2:42 am
So if he gets to the coincidentally correct answer on the basis of incorrect data then he can avoid a round trip by assuming that he got something right and not checking.
Not a developer that I would wish to hire.
September 15, 2015 at 2:48 am
Real developers use HASHBYTES 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 15, 2015 at 3:02 am
Eirikur Eiriksson (9/15/2015)
Koen Verbeeck (9/15/2015)
Real developers use HASHBYTES 😀Same column order rule applies there too;-)
😎
Yeah, but less collisions.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 15, 2015 at 3:48 am
Koen Verbeeck (9/15/2015)
Yeah, but less collisions.
Naive question, but isn't the problem any POSSIBILITY of collisions?
September 15, 2015 at 3:53 am
Kristen-173977 (9/15/2015)
Koen Verbeeck (9/15/2015)
Yeah, but less collisions.Naive question, but isn't the problem any POSSIBILITY of collisions?
Depends on what you are doing.
Suppose that I am running a daily ETL. I uses hashes to determine if a row should be updated or not in a dimension.
In the weekend, I update all rows anyway, regardless if they have changed or not in order to rule out hash collisions.
You probably want a hash algorithm that can last without collisions until the weekend 🙂
But yes, even if the possibility/probability is lower, it's still possible to have a collision on the first row if you have bad luck 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 15, 2015 at 3:53 am
I might be misunderstanding this, but I thought:
CHECKSUM will not always return a different result when the values in the expression list change. Therefore it cannot be used to check 'to see if a user has updated a row'. You could maybe use it to check if it was very likely that a user hasn't updated a row, i.e. the same checksum probably (but not definitely) means the row hasn't changed - not sure what use that would be.
The answer is 'No, never' because if Bobby is truly 'checking to see if a user has updated a row' he cannot use the CHECKSUM query and cannot avoid round trips.
PS Not sure what 'his columns are in the wrong order' means - they're in a different order, but how does that affect the ability to use CHECKSUM to see if a row has been updated?
September 15, 2015 at 4:11 am
sipas (9/15/2015)
PS Not sure what 'his columns are in the wrong order' means - they're in a different order, but how does that affect the ability to use CHECKSUM to see if a row has been updated?
Because the checksums of the table and the ETL application will always be different due to the different order of columns, except in the unlikely case of a collision.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 15, 2015 at 4:25 am
Koen Verbeeck (9/15/2015)
sipas (9/15/2015)
PS Not sure what 'his columns are in the wrong order' means - they're in a different order, but how does that affect the ability to use CHECKSUM to see if a row has been updated?Because the checksums of the table and the ETL application will always be different due to the different order of columns, except in the unlikely case of a collision.
If Bobby is directly comparing the checksums of the table and the ETL and expecting them to be equal then as you say it will almost never give the correct answer; I assumed he wasn't expecting the result of the subtraction to be zero, just that it had changed. But even if they were in the right order it isn't guaranteed to give the correct answer, so either way you can't use CHECKSUM.
September 15, 2015 at 4:31 am
Trying this out :Whistling:, I'm wondering on what kind of logic Bobby is using.....
😎
USE tempdb;
GO
SET NOCOUNT ON;
;WITH T(N) AS (SELECT CONVERT(TINYINT,N,0) FROM (VALUES (1),(2),(3),(4),(5))AS X(N))
,BASE_DATA AS
(
SELECT
T1.N AS N1
,T2.N AS N2
,T3.N AS N3
,SUM(T1.N + T2.N + T3.N) AS SMM
,SUM(T1.N * 100 + T2.N * 10 + T3.N) AS SXM
,CHECKSUM(T1.N,T2.N,T3.N) AS CH1
,CHECKSUM(T3.N,T2.N,T1.N) AS CH2
,CHECKSUM(T2.N,T3.N,T1.N) AS CH3
,CHECKSUM(T3.N,T1.N,T3.N) AS CH4
FROM T T1,T T2,T T3
GROUP BY T1.N
,T2.N
,T3.N
)
SELECT
BD.N1
,BD.N2
,BD.N3
,BD.SMM
,BD.SXM
,BD.CH1
,BD.CH2
,BD.CH3
,BD.CH4
FROM BASE_DATA BD;
Output
N1 N2 N3 SMM SXM CH1 CH2 CH3 CH4
---- ---- ---- ----------- ----------- ----------- ----------- ----------- -----------
1 1 1 3 111 273 273 273 273
1 1 2 4 112 274 529 289 530
1 1 3 5 113 275 785 305 787
1 1 4 6 114 276 1041 321 1044
1 1 5 7 115 277 1297 337 1301
1 2 1 4 121 289 289 529 273
1 2 2 5 122 290 545 545 530
1 2 3 6 123 291 801 561 787
1 2 4 7 124 292 1057 577 1044
1 2 5 8 125 293 1313 593 1301
1 3 1 5 131 305 305 785 273
1 3 2 6 132 306 561 801 530
1 3 3 7 133 307 817 817 787
1 3 4 8 134 308 1073 833 1044
1 3 5 9 135 309 1329 849 1301
1 4 1 6 141 321 321 1041 273
1 4 2 7 142 322 577 1057 530
1 4 3 8 143 323 833 1073 787
1 4 4 9 144 324 1089 1089 1044
1 4 5 10 145 325 1345 1105 1301
1 5 1 7 151 337 337 1297 273
1 5 2 8 152 338 593 1313 530
1 5 3 9 153 339 849 1329 787
1 5 4 10 154 340 1105 1345 1044
1 5 5 11 155 341 1361 1361 1301
2 1 1 4 211 529 274 274 289
2 1 2 5 212 530 530 290 546
2 1 3 6 213 531 786 306 803
2 1 4 7 214 532 1042 322 1060
2 1 5 8 215 533 1298 338 1317
2 2 1 5 221 545 290 530 289
2 2 2 6 222 546 546 546 546
2 2 3 7 223 547 802 562 803
2 2 4 8 224 548 1058 578 1060
2 2 5 9 225 549 1314 594 1317
2 3 1 6 231 561 306 786 289
2 3 2 7 232 562 562 802 546
2 3 3 8 233 563 818 818 803
2 3 4 9 234 564 1074 834 1060
2 3 5 10 235 565 1330 850 1317
2 4 1 7 241 577 322 1042 289
2 4 2 8 242 578 578 1058 546
2 4 3 9 243 579 834 1074 803
2 4 4 10 244 580 1090 1090 1060
2 4 5 11 245 581 1346 1106 1317
2 5 1 8 251 593 338 1298 289
2 5 2 9 252 594 594 1314 546
2 5 3 10 253 595 850 1330 803
2 5 4 11 254 596 1106 1346 1060
2 5 5 12 255 597 1362 1362 1317
3 1 1 5 311 785 275 275 305
3 1 2 6 312 786 531 291 562
3 1 3 7 313 787 787 307 819
3 1 4 8 314 788 1043 323 1076
3 1 5 9 315 789 1299 339 1333
3 2 1 6 321 801 291 531 305
3 2 2 7 322 802 547 547 562
3 2 3 8 323 803 803 563 819
3 2 4 9 324 804 1059 579 1076
3 2 5 10 325 805 1315 595 1333
3 3 1 7 331 817 307 787 305
3 3 2 8 332 818 563 803 562
3 3 3 9 333 819 819 819 819
3 3 4 10 334 820 1075 835 1076
3 3 5 11 335 821 1331 851 1333
3 4 1 8 341 833 323 1043 305
3 4 2 9 342 834 579 1059 562
3 4 3 10 343 835 835 1075 819
3 4 4 11 344 836 1091 1091 1076
3 4 5 12 345 837 1347 1107 1333
3 5 1 9 351 849 339 1299 305
3 5 2 10 352 850 595 1315 562
3 5 3 11 353 851 851 1331 819
3 5 4 12 354 852 1107 1347 1076
3 5 5 13 355 853 1363 1363 1333
4 1 1 6 411 1041 276 276 321
4 1 2 7 412 1042 532 292 578
4 1 3 8 413 1043 788 308 835
4 1 4 9 414 1044 1044 324 1092
4 1 5 10 415 1045 1300 340 1349
4 2 1 7 421 1057 292 532 321
4 2 2 8 422 1058 548 548 578
4 2 3 9 423 1059 804 564 835
4 2 4 10 424 1060 1060 580 1092
4 2 5 11 425 1061 1316 596 1349
4 3 1 8 431 1073 308 788 321
4 3 2 9 432 1074 564 804 578
4 3 3 10 433 1075 820 820 835
4 3 4 11 434 1076 1076 836 1092
4 3 5 12 435 1077 1332 852 1349
4 4 1 9 441 1089 324 1044 321
4 4 2 10 442 1090 580 1060 578
4 4 3 11 443 1091 836 1076 835
4 4 4 12 444 1092 1092 1092 1092
4 4 5 13 445 1093 1348 1108 1349
4 5 1 10 451 1105 340 1300 321
4 5 2 11 452 1106 596 1316 578
4 5 3 12 453 1107 852 1332 835
4 5 4 13 454 1108 1108 1348 1092
4 5 5 14 455 1109 1364 1364 1349
5 1 1 7 511 1297 277 277 337
5 1 2 8 512 1298 533 293 594
5 1 3 9 513 1299 789 309 851
5 1 4 10 514 1300 1045 325 1108
5 1 5 11 515 1301 1301 341 1365
5 2 1 8 521 1313 293 533 337
5 2 2 9 522 1314 549 549 594
5 2 3 10 523 1315 805 565 851
5 2 4 11 524 1316 1061 581 1108
5 2 5 12 525 1317 1317 597 1365
5 3 1 9 531 1329 309 789 337
5 3 2 10 532 1330 565 805 594
5 3 3 11 533 1331 821 821 851
5 3 4 12 534 1332 1077 837 1108
5 3 5 13 535 1333 1333 853 1365
5 4 1 10 541 1345 325 1045 337
5 4 2 11 542 1346 581 1061 594
5 4 3 12 543 1347 837 1077 851
5 4 4 13 544 1348 1093 1093 1108
5 4 5 14 545 1349 1349 1109 1365
5 5 1 11 551 1361 341 1301 337
5 5 2 12 552 1362 597 1317 594
5 5 3 13 553 1363 853 1333 851
5 5 4 14 554 1364 1109 1349 1108
5 5 5 15 555 1365 1365 1365 1365
September 15, 2015 at 5:31 am
Yeah, I didn't quite get it either. I think there will likely be some lively debate on this one.
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply