1

I have a weird problem on BINARY_CHECKSUM function of SQL Server. Below code (just copy and query it on your management studio) returns different checksum values for different versions of SQL Server on different machines (I'm not sure whether that occurs because of version difference or server difference).

Notes :

  • The value is a binary file actually, that's stored in SQL Server. But that's not the point I think, value is there itself

  • Query returns value of 1475829670 on server 1 (with SQL Server 2005)

  • And it returns value of 1024353818 on server 2 (with SQL Server 2008)

  • And it returns value of 1024353818 on server 3 (with SQL Server 2012)

Query is:

select 
    BINARY_CHECKSUM(0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300F53DEF550000000000000000E00002210B0108000028000000060000000000005E46000000200000006000000000400000200000000200000400000000000000040000000000000000A0000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000000C4600004F00000000600000C002000000000000000000000000000000000000008000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000064260000002000000028000000020000000000000000000000000000200000602E72737263000000C00200000060000000040000002A0000000000000000000000000000400000402E72656C6F6300000C0000000080000000020000002E0000000000000000000000000000400000420000000000000000000000000000000040460000000000004800000002000500082D00000419000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001E02280300000A2A4E027C010000047B0400000A6F0500000A26172A2602037D01000004172A000003300500BD00000000000000027C010000047C0600000A7B0700000A720100007016720900007072090000706F0800000A26027C010000047C0600000A7B0900000A7201000070166F0A00000A26027C010000047C0600000A7B0B00000A7201000070176F0C00000A26027C010000047C0600000A7B0700000A720B00007016720900007072090000706F0800000A26027C010000047C0600000A7B0900000A720B000070166F0A00000A26027C010000047C0600000A7B0B00000A720B000070176F0C00000A262A000000133005000A010000010000111200FE15040000011200177D0D00000A0F017B0E00000A1F0A331412000228070000068C0F0000017D0F00000A062A0F017B0E00000A20C900000033261200020F017B1000000A169A74100000010F017B1000000A179A28080000067D0F00000A062A0F017B0E00000A1F7A3316020F017B1000000A169AA5110000012809000006062A0F017B0E00000A1F6533221200020F017B1000000A169AA511000001280A0000068C0F0000017D0F00000A062A0F017B0E00000A1F7C330802280B000006062A0F017B0E00000A1F793316020F017B1000000A169AA511000001280C000006062A0F017B0E00000A1F0F3308022804000006062A1200167D0D00000A1200147D0F00000A062ADA031F0A3302172A0320C90000003302172A031F7A3302172A031F653302172A031F7C3302172A031F793302172A031F0F3302172A162A000000133005006104000002000011027C010000047C1100000A7B1200000A72170000706F1300000A027C010000047C1100000A7B1400000A722B0000706F1500000A72550000700A067284010070281600000A0A067202020070281600000A0A06727C020070281600000A0A0672EA020070281600000A0A067279030070281600000A0A0672F7030070281600000A0A027C010000047C1100000A7B1700000A066F1800000A027C010000047C1100000A7B1900000A720B0000706F1A00000A027C010000047C0600000A7B0700000A7210050070167209000070146F0800000A26027C010000047C0600000A7B0700000A722A050070167209000070146F0800000A26027C010000047C0600000A7B0700000A723A0500701F0C723A050070146F0800000A26027C010000047C0600000A7B1B00000A723A050070166F1C00000A26027C010000047C0600000A7B0700000A7244050070167244050070146F0800000A26027C010000047C0600000A7B0700000A7260050070167260050070146F0800000A26027C010000047C0600000A7B0700000A727E05007016727E050070146F0800000A26027C010000047C0600000A7B0700000A72A20500701672A2050070146F0800000A26027C010000047C0600000A7B0700000A72C00500701672C0050070146F0800000A26027C010000047C0600000A7B0700000A72E20500701F0A72E2050070146F0800000A26027C010000047C0600000A7C1D00000A7B1E00000A72E20500707202060070721A0600706F1F00000A26027C010000047C0600000A7C1D00000A7B1E00000A72E20500707234060070724E0600706F1F00000A26027C010000047C0600000A7B0700000A726A0600701F0A726A060070728C0600706F0800000A26027C010000047C0600000A7C1D00000A7B1E00000A726A060070728C06007072940600706F1F00000A26027C010000047C0600000A7C1D00000A7B1E00000A726A06007072B406007072BE0600706F1F00000A26027C010000047C0600000A7B0700000A72D80600701772D806007072090000706F0800000A26027C010000047C0600000A7B0700000A72F60600701972F6060070168C0F0000016F0800000A26027C010000047C0600000A7B0700000A721C07007019721C070070178C0F0000016F0800000A26027C010000047C0600000A7B0700000A72480700701B7248070070146F0800000A26027C010000047C0600000A7B0700000A72720700701F0F7272070070168C0F0000016F0800000A26027C010000047C0600000A7B0700000A72A00700701F0F72A0070070168C0F0000016F0800000A26027C010000047C0600000A7B0900000A7248070070166F0A00000A26027C010000047C0600000A7C2000000A7B2100000A72A007007072D60700706F2200000A26027C010000047C0600000A7C2000000A7B2100000A727207007072140800706F2200000A26027C010000047C1100000A7B2300000A166F2400000A027C010000047C1100000A7B2500000A166F2600000A027C010000047C1100000A7B2700000A166F2800000A172A000000033003000D020000000000000372F6060070282900000A39AC010000027C010000047C0600000A7B2A00000A72F60600706F2B00000A3AC9000000027C010000047C0600000A7B0900000A726A060070166F0A00000A26027C010000047C0600000A7B0900000A72D8060070166F0A00000A26027C010000047C0600000A7B0900000A72E2050070166F0A00000A26027C010000047C0600000A7B0900000A7244050070166F0A00000A26027C010000047C0600000A7B0900000A72A2050070166F0A00000A26027C010000047C0600000A7B0900000A7260050070166F0A00000A26027C010000047C0600000A7B0900000A727E050070166F0A00000A2638C4000000027C010000047C0600000A7B0900000A726A060070176F0A00000A26027C010000047C0600000A7B0900000A72D8060070176F0A00000A26027C010000047C0600000A7B0900000A72E2050070176F0A00000A26027C010000047C0600000A7B0900000A7244050070176F0A00000A26027C010000047C0600000A7B0900000A72A2050070176F0A00000A26027C010000047C0600000A7B0900000A7260050070176F0A00000A26027C010000047C0600000A7B0900000A727E050070176F0A00000A260372A0070070282900000A2C4204282C00000A2C1E027C010000047C0600000A7B0900000A7248070070176F0A00000A262B1C027C010000047C0600000A7B0900000A7248070070166F0A00000A26142A00000013300400FE01000003000011027C010000047C2D00000A7B2E00000A72580800706F2F00000A7280080070282900000A2C79027C010000047C3000000A7C3100000A7B3200000A6F3300000A027C010000047C3000000A7C3100000A7B3400000A72F6060070027C010000047C0600000A7B2A00000A72F60600706F2B00000A8C0F0000016F3500000A027C010000047C3000000A7C3100000A7B3600000A7288080070036F3700000A26027C010000047C2D00000A7B2E00000A72580800706F2F00000A7298080070282900000A3910010000027C010000047C2D00000A7B2E00000A72A20800706F2F00000A0A027C010000047C2D00000A7B2E00000A72E20800706F2F00000A26722209007006722A090070283800000A0B72090000700C027C010000047C0600000A7B2A00000A72F60600706F2B00000A0D027C010000047C0600000A7B3900000A723A0500706F3A00000A1304092C331A8D290000011305110516723A090070A211051707A2110518724A090070A2110519038C11000001A21105283B00000A0C2B321A8D290000011306110616723A090070A211061707A211061872CF090070A211061911048C11000001A21106283B00000A0C087209000070283C00000A2C17027C010000047C3000000A7B3D00000A086F3E00000A26027C010000047C3F00000A7C4000000A7B4100000A72330A007072490A00706F4200000A262A0A172A000000133003002900000004000011027C010000047C4300000A7B4400000A723A050070166F4500000A284600000A0A0206280D0000062A000000133003002900000004000011027C010000047C4300000A7B4400000A723A050070166F4500000A284600000A0A0206280D0000062A000000033006005901000000000000027C010000047C3000000A7B4700000A72590A0070725D0A00700F01284800000A281600000A6F4900000A26027C010000047C0600000A7B4A00000A723A050070027C010000047C3000000A7B4B00000A72590A007072FA0A0070166F4C00000A8C110000016F4D00000A26027C010000047C0600000A7B4A00000A7201000070027C010000047C3000000A7B4E00000A72590A00707201000070166F4F00000A6F4D00000A26027C010000047C0600000A7B4A00000A720B000070027C010000047C3000000A7B4E00000A72590A0070720B000070166F4F00000A6F4D00000A26027C010000047C0600000A7B4A00000A7210050070027C010000047C3000000A7B4E00000A72590A00707210050070166F4F00000A6F4D00000A26027C010000047C0600000A7B4A00000A722A050070027C010000047C3000000A7B4E00000A72590A0070722A050070166F4F00000A6F4D00000A262A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C000000C4040000237E0000300500001407000023537472696E677300000000440C0000000B0000235553004417000010000000234755494400000054170000B001000023426C6F620000000000000002000001571702000900000000FA013300160000010000003300000002000000010000000D00000009000000010000004F0000000200000004000000010000000200000000000A0001000000000006003C0035000A0065004F000A0087004F000A00B4004F000A00C9004F000A000101E5000600D301B3010600F301B3010A00410221020A00820264020A00910221020A00A002E5000A00AB0221020A00C80221020600F702350006000C0335000600130335000A00190364020A002D0321020A00580321020A008A0321020A00B30321020A00DA0321020A00F70364020A00100421020A002C0464020A00400421020A005F0421020A008E0421020A00BF0421020A00050521020600260535000A00410564020A00600521020A00870564020A00920564020A00A20521020A00B60521020A00D20521020A00E60521020600050635000A001A0621020A002F0664020A00420664020A00500621020A00770664020A00980621020A00BC0621020A00D60621020A00EF0621020A00000721020000000001000000000001000100010010001E000000050001000100010097001200502000000000861876000A000100582000000000E6017C000E0001006C2000000000E6019B00160001007820000000008600A5000A000200442100000000E601DB001C0002005A2200000000E6010A0123000300942200000000860016010E000400042700000000860027012900040020290000000086003C012F0006002A2B000000008600540134000700302B00000000860074010A000800682B0000000086008B012F000800A02B000000008100A3012F000900000001005F0200000100E70200000100F10200000100F40400000200FF0200000100380500000100380500000100380500000100B90602000900390076002F00410076000A00090076000A00190050023900490058020E0019008A023D0051009C0241005900580245005100BD024E006900580252005100DB0258007100580252002100EB025C002900F1025F002100FF026300290005036600190024036F00910048037300990058027700910073037C00A100580277008100830380009100A4038600A900580277009100CC038A00B100580277005100EC038E00B90058025200510004049200C10024049600C90058029A0051003704A100D1005404A500D9005802A90091007C04AF00E1005802B3009100AC04B800E9005802B3009100DF04BC00F1005802B3008100F904C40051001905CA00F9005802CE0001012E05D30019005105D80009017B05DD0011015802E20019008D05E70019019D05EC002101B105F100290158020A002101C905F60031015802FB002101E1050101390158020601810083030C015100F9051301410158021801810083031D0181000C06C40019012706230151015802CE00190039062801590197002D0161016A0632016901580229001900880643017101AD064801790158024D010101B10653011901C7065C018900CD06610181015802A9005100E60665011901F9056A01910158026F0189015802760119017B057C019901580281012E000B0088012E00130091016A00C000370158010480000000000000000000000000000000001102000002000000000000000000000001002C000000000001000000000000000000000000004F00000000000000003C4D6F64756C653E00434F44452E45465F434854414E494D2E646C6C004C454441465F434F4445455854006D73636F726C69620053797374656D004D61727368616C42795265664F626A656374004C45444170704672616D65776F726B2E547970657300494C45444170704672616D65776F726B002E63746F720041534D5245465245534800744C4544414646554E4354494F4E53004C414600494E49545459504553004C4F41444F424A4543544C49535400494C41465F43414C4C4556454E54524553554C5400494C41465F43414C4C4556454E544152470043414C4C4556454E54004C45444170704672616D65776F726B2E54797065732E456E756D73004556454E54444543004556454E5445584953545300464F524D4556454E545F4F4E494E495400454449544F524556454E545F4F4E4348414E47450041464556454E545F4146544552534156455245434F52440041464556454E545F4F4E434845434B53415645524551554952454D454E54530041464556454E545F41465445524E45575245434F52440041464556454E545F41465445524C4F41445245434F5244006361726962696C67695F79756B6C650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500434F44452E45465F434854414E494D004C45444170704672616D65776F726B2E54797065732E44656C656761746573004C41465F5245465245534841534D005245465245534800496E766F6B650046554E43004C45444170704672616D65776F726B2E54797065732E5374727563747300454449544F525300454449544F5200454449544F525F4144440041444400454449544F525459504500454449544F525F534554454E41424C454400534554454E41424C454400454449544F525F534554524541444F4E4C5900534554524541444F4E4C59004152470056414C4944004556454E5400426F6F6C65616E0056414C55450056414C55455300537472696E6700496E7433320050524F504552544945530050524F50455254590050524F504552544945535F5345545F4445434C45524154494F4E005345545F4445434C45524154494F4E0050524F504552544945535F5345545F44425441424C454E414D45005345545F44425441424C454E414D4500436F6E6361740050524F504552544945535F5345545F4442564945574E414D45005345545F4442564945574E414D450050524F504552544945535F5345545F4E414D454649454C44005345545F4E414D454649454C4400454449544F525F53455456495349424C450053455456495349424C45004C4F4F4B5550434F4D424F53004C4F4F4B5550434F4D424F004C4F4F4B5550434F4D424F5F4144444954454D004144444954454D00434845434B424F58455300434845434B424F5800434845434B424F585F53455443415054494F4E0053455443415054494F4E0050524F504552544945535F5345545F53484F574E4557425554544F4E005345545F53484F574E4557425554544F4E0050524F504552544945535F5345545F53484F574C4F4144425554544F4E005345545F53484F574C4F4144425554544F4E0050524F504552544945535F5345545F53484F5744454C455445425554544F4E005345545F53484F5744454C455445425554544F4E004E414D45006F705F457175616C69747900454449544F525F47455456414C5545424F4F4C0047455456414C5545424F4F4C00436F6E7665727400546F426F6F6C65616E005245434F524449440053595354454D5641524941424C45530053595354454D5641524941424C450053595354454D5641524941424C455F47455456414C55455354520047455456414C554553545200444154415300444154410044415441535F53415645005341564500444154415F534156455F494E495400494E495400444154415F534156455F41444456414C55450041444456414C554500444154415F534156455F45584543004558454300454449544F525F47455456414C5545494E540047455456414C5545494E54004F626A656374006F705F496E657175616C69747900444154415F4558454355544500455845435554450045585445524E414C530045585445524E414C0045585445524E414C5F4C4146530045585445524E414C5F4C41465F53454E445F434F4D4D414E440053454E445F434F4D4D414E440043414C4C4552504152414D45544552530043414C4C4552504152414D455445520043414C4C4552504152414D45544552535F4745540047455400546F496E74333200696400444154415F515545525900515545525900546F537472696E6700454449544F525F53455456414C55450053455456414C554500444154415F47455456414C5545494E5400444154415F47455456414C55455354520000000000074B004F0044000001000B55004E00560041004E000013430061007200690020004B0061007200740000296C006500640065006600610074007500720061002E0063006100720069006400650074006100790000812D2800730065006C0065006300740020002A002000660072006F006D0020002800730065006C006500630074002000640074002E00490044002C00640074002E0043004800490044002C00640074002E0045004600410054005500520041005F0055004E00560041004E002C00640074002E0045004600410054005500520041005F00540043004B004E00410044002C00640074002E0045004600410054005500520041005F00540043004B004E0053004F005900410044002C00640074002E0045004600410054005500520041005F0053005500420045004E004F002C00640074002E0045004600410054005500520041005F00530045004E004100520059004F002C00640074002E0045004600410054005500520041005F005300410054004900430049004E004F002000007D2C00640074002E0045004600410054005500520041005F00560045005200470049005400490050002C00640074002E0045004600410054005500520041005F004500540049004B00450054002C00640074002E0045004600410054005500520041005F004200410053004C0041004D0041005400410052004900480000792C00490053004E0055004C004C002800640074002E0045004600410054005500520041005F00530055004E005500430055004B004F004E00540052004F004C002C0031002900200061007300200045004600410054005500520041005F00530055004E005500430055004B004F004E00540052004F004C00006D2C00490053004E0055004C004C002800640074002E0045004600410054005500520041005F004B0055004C004C0041004E00490059004F0052002C0030002900200061007300200045004600410054005500520041005F004B0055004C004C0041004E00490059004F00520000808D2C00490053004E0055004C004C002800640074002E0045004600410054005500520041005F004200410053004C0041004D004100540041005200490048004B0055004C004C0041004E002C0030002900200061007300200045004600410054005500520041005F004200410053004C0041004D004100540041005200490048004B0055004C004C0041004E00007D2C00490053004E0055004C004C002800640074002E0045004600410054005500520041005F004E0045005400540055005400410052004B0055004C004C0041004E002C0030002900200061007300200045004600410054005500520041005F004E0045005400540055005400410052004B0055004C004C0041004E000081172C00630068002E004B004F0044002C00630068002E0055004E00560041004E002C00630068002E005600450052004700490044004100490052004500530049002C00630068002E00560045005200470049004E004F002000660072006F006D0020006C006500640065006600610074007500720061002E006300610072006900640065007400610079002000640074002C006C006500640065006600610074007500720061002E0077006300610072006900740061006E0069006D002000630068002000770068006500720065002000630068002E00490044003D00640074002E004300480049004400290020006100730020006D0079007400610062006C00650029006100730020007400610062006C00650031000019560045005200470049004400410049005200450053004900000F560045005200470049004E004F0000094300480049004400001B45004600410054005500520041005F0055004E00560041004E00001D45004600410054005500520041005F00540043004B004E0041004400002345004600410054005500520041005F00540043004B004E0053004F00590041004400001D45004600410054005500520041005F0053005500420045004E004F00002145004600410054005500520041005F005300410054004900430049004E004F00001F45004600410054005500520041005F00530045004E004100520059004F000017540045004D0045004C004600410054005500520041000019540065006D0065006C0020004600610074007500720061000019540049004300410052004900460041005400550052004100001B5400690063006100720069002000460061007400750072006100002145004600410054005500520041005F0056004500520047004900540049005000000756004B004E00001F5600650072006700690020004B0069006D006C0069006B0020004E006F000009540043004B004E000019540043002E004B0069006D006C0069006B0020004E006F00001D45004600410054005500520041005F004500540049004B0045005400002545004600410054005500520041005F004B0055004C004C0041004E00490059004F005200002B45004600410054005500520041005F00530055004E005500430055004B004F004E00540052004F004C00002945004600410054005500520041005F004200410053004C0041004D00410054004100520049004800002D45004600410054005500520041005F004E0045005400540055005400410052004B0055004C004C0041004E00003545004600410054005500520041005F004200410053004C0041004D004100540041005200490048004B0055004C004C0041004E00003D420061005F016C0061006E0067003101E70020005400610072006900680069006E00690020005200650066006500720061006E007300200041006C0001434B0061006C0065006D00200054007500740061007200200041006C0061006E0031016E00640061006E002000300173006B006F006E0074006F0020004400FC005F01012745004600410054005500520041005F0055005900470055004C0041004D0041004B004F00440000074C0045004400000F63006800740061006E0069006D0000094C004F0047004F00003F45004600410054005500520041005F0055005900470055004C0041004D0041004B004F0044005F004C004F0047004F004600490052004D0041004E004F00003F45004600410054005500520041005F0055005900470055004C0041004D0041004B004F0044005F004C004F0047004F0044004F004E0045004D004E004F0000074C0047005F00000F5F0043004C004300410052004400000F75007000640061007400650020000080832000730065007400200044004500460049004E004900540049004F004E0032003D00270045002D0046006100740075007200610027002C0053005000450043004F004400450035003D00270045002D00460061007400750072006100270020007700680065007200650020004C004F0047004900430041004C005200450046003D0001632000730065007400200044004500460049004E004900540049004F004E0032003D00270027002C0053005000450043004F004400450035003D002700270020007700680065007200650020004C004F0047004900430041004C005200450046003D000115450046005F00430048004C004900530054004500000F52004500460052004500530048000003710000809B530045004C004500430054002000490044002C004B004F0044002C0055004E00560041004E002C005600450052004700490044004100490052004500530049002C00560045005200470049004E004F002000660072006F006D0020006C006500640065006600610074007500720061002E0077006300610072006900740061006E0069006D002000770068006500720065002000490044003D0000054900440000B8F9035DD979D74FBA5225767E1589DF0008B77A5C561934E08903200001032000020306110D05200102110D062001111111150520010211190520021C0E1C0420010108042001020803061225030611290306122D082004020E11310E1C03061235052002020E02030612390206020306111902061C03061D1C0407011111030611490306124D042001010E030612510500020E0E0E03061255030612590306125D0306116103061265062003020E0E0E030611690306126D052002020E0E03061271042001010203061275030612790307010E050002020E0E0306127D042001020E040001021C040611808504061280890420010E0E040611808D040611809104061280950406128099052002010E1C040612809D052002080E080600030E0E0E0E04061280A1042001080E0500010E1D1C04061280A904061180AD04061180B104061280B50B07070E0E0E02081D1C1D1C04061180B904061280BD0520020E0E08040001080E0307010804061280C10320000E04061280C504061280C9062003080E0E08052002020E1C04061280CD0620030E0E0E080801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773013446000000000000000000004E46000000200000000000000000000000000000000000000000000040460000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058600000640200000000000000000000640234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004C4010000010053007400720069006E006700460069006C00650049006E0066006F000000A001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000048001400010049006E007400650072006E0061006C004E0061006D006500000043004F00440045002E00450046005F0043004800540041004E0049004D002E0064006C006C0000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000005000140001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000043004F00440045002E00450046005F0043004800540041004E0049004D002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004000000C000000603600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000)

I've been stuck at here for hours. Thanks for any help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ender KARADAG
  • 87
  • 2
  • 11
  • Though the list of [breaking changes in SQL Server 2008](https://msdn.microsoft.com/en-us/library/ms143179(v=sql.100).aspx) doesn't mention `BINARY_CHECKSUM`, your evidence suggests that the algorithm has changed. – Vladimir Baranov Nov 01 '15 at 10:21
  • SQL Server 2014 and SQL Server 2016 CTP 3 also return `1024353818` – marc_s Nov 01 '15 at 12:15
  • Another thing is, i get this weird result for just 2 of 132 this kind large values. We use BINARY_CHECKSUM for a file update service. But this server 1 (with mssql2005) always downloads the file at all update checks. still looking for a solution:/ – Ender KARADAG Nov 01 '15 at 14:26

1 Answers1

2

For byte change validation, you should be using HASHBYTES, which has a published list of the algorithms used in generating their hash.

BINARY_CHECKSUM, just like CHECKSUM is susceptible to missing changes, is not to be used for this purpose. From the Remarks section of the CHECKSUM article.

However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change. Consider using HashBytes instead. When an MD5 hash algorithm is specified, the probability of HashBytes returning the same result for two different inputs is much lower than that of CHECKSUM.

As Vladimir Baranov comment pointed out, the behavior likely changed after SQL 2005, but regardless, the purpose for any of the CHECKSUM methods are for "hash indexing", so uniqueness is never guaranteed...which it is never guaranteed for any hashing algorithm, each only attempt to improve the probabilities that you will not find two different values with the same hash.

Andrew Loree
  • 263
  • 1
  • 8
  • 2
    Thank you for your comment Andrew, Actually im not expecting a guarantee about uniqueness. if uniqueness should be guaranteed on a hash algorthym, we could produce input from hash value. My problem is; im NOT getting "same result for different value", but im getting "different results for same value" But ive tried HASHBYTES approach. using BINARY_CHECKSUM(HASHBYTES(X)) may solve my situation. Im testing it now over different versions. Best Regards – Ender KARADAG Nov 01 '15 at 18:30
  • Which is why you MUST use a documented/well known algorithm, whose behavior does not change. Both of the `CHECKSUM` functions are just the opposite. Microsoft appears to have changed the behavior between versions, which I would guess for performance. To clarify your last comment, you should be using the returned value of `HASHBYTES` alone. CHECKSUM'ing the returned value will likely produce the same unpredictable results. – Andrew Loree Nov 01 '15 at 19:39
  • Thank you Andrew. My example is about 12000 bytes long, but HASHBYTES wants 4000 bytes at most. HASHBYTES gives a result when converted to nvarchar(4000). But im afraid about that. What would ur advice be at this point? – Ender KARADAG Nov 01 '15 at 19:55
  • Take a look at [SQL Server 2008 and HashBytes](http://stackoverflow.com/questions/3717977/sql-server-2008-and-hashbytes). It applies to using MAX data types, if your data is larger than the 8K page size for (var)binary. – Andrew Loree Nov 01 '15 at 20:14