Does SQL Server CheckSum calculate a CRC? If not how can I get MS SQL to calculate a CRC on an arbitrary varchar column?
I apologize for the crudity of the model, but this seems to do a correct CRC32 calculation.I'm not a TSQL expert, and I'm sure that this could be improved mightily by a real SQL Server pro...
@input is the variable to calculate the CRC32 on.It should be trivial to package this as a sproc or a udf, and the lookup table could be factored out to a permanent table (or even calculated on the fly).
Anyway, it seems to work. I'd be interested to see any improvements, as it's always good to learn new tricks :)
EDIT: I have checked my results against http://crc32-checksum.waraxe.us/ and it seems good so far.
Andrew
DECLARE @input VARCHAR(50)SET @input = 'test'SET NOCOUNT ONDECLARE @tblLookup TABLE (ID INT IDENTITY(0,1) NOT NULL, Value BIGINT)INSERT INTO @tblLookup VALUES (0)INSERT INTO @tblLookup VALUES (1996959894)INSERT INTO @tblLookup VALUES (3993919788)INSERT INTO @tblLookup VALUES (2567524794)INSERT INTO @tblLookup VALUES (124634137)INSERT INTO @tblLookup VALUES (1886057615)INSERT INTO @tblLookup VALUES (3915621685)INSERT INTO @tblLookup VALUES (2657392035)INSERT INTO @tblLookup VALUES (249268274)INSERT INTO @tblLookup VALUES (2044508324)INSERT INTO @tblLookup VALUES (3772115230)INSERT INTO @tblLookup VALUES (2547177864)INSERT INTO @tblLookup VALUES (162941995)INSERT INTO @tblLookup VALUES (2125561021)INSERT INTO @tblLookup VALUES (3887607047)INSERT INTO @tblLookup VALUES (2428444049)INSERT INTO @tblLookup VALUES (498536548)INSERT INTO @tblLookup VALUES (1789927666)INSERT INTO @tblLookup VALUES (4089016648)INSERT INTO @tblLookup VALUES (2227061214)INSERT INTO @tblLookup VALUES (450548861)INSERT INTO @tblLookup VALUES (1843258603)INSERT INTO @tblLookup VALUES (4107580753)INSERT INTO @tblLookup VALUES (2211677639)INSERT INTO @tblLookup VALUES (325883990)INSERT INTO @tblLookup VALUES (1684777152)INSERT INTO @tblLookup VALUES (4251122042)INSERT INTO @tblLookup VALUES (2321926636)INSERT INTO @tblLookup VALUES (335633487)INSERT INTO @tblLookup VALUES (1661365465)INSERT INTO @tblLookup VALUES (4195302755)INSERT INTO @tblLookup VALUES (2366115317)INSERT INTO @tblLookup VALUES (997073096)INSERT INTO @tblLookup VALUES (1281953886)INSERT INTO @tblLookup VALUES (3579855332)INSERT INTO @tblLookup VALUES (2724688242)INSERT INTO @tblLookup VALUES (1006888145)INSERT INTO @tblLookup VALUES (1258607687)INSERT INTO @tblLookup VALUES (3524101629)INSERT INTO @tblLookup VALUES (2768942443)INSERT INTO @tblLookup VALUES (901097722)INSERT INTO @tblLookup VALUES (1119000684)INSERT INTO @tblLookup VALUES (3686517206)INSERT INTO @tblLookup VALUES (2898065728)INSERT INTO @tblLookup VALUES (853044451)INSERT INTO @tblLookup VALUES (1172266101)INSERT INTO @tblLookup VALUES (3705015759)INSERT INTO @tblLookup VALUES (2882616665)INSERT INTO @tblLookup VALUES (651767980)INSERT INTO @tblLookup VALUES (1373503546)INSERT INTO @tblLookup VALUES (3369554304)INSERT INTO @tblLookup VALUES (3218104598)INSERT INTO @tblLookup VALUES (565507253)INSERT INTO @tblLookup VALUES (1454621731)INSERT INTO @tblLookup VALUES (3485111705)INSERT INTO @tblLookup VALUES (3099436303)INSERT INTO @tblLookup VALUES (671266974)INSERT INTO @tblLookup VALUES (1594198024)INSERT INTO @tblLookup VALUES (3322730930)INSERT INTO @tblLookup VALUES (2970347812)INSERT INTO @tblLookup VALUES (795835527)INSERT INTO @tblLookup VALUES (1483230225)INSERT INTO @tblLookup VALUES (3244367275)INSERT INTO @tblLookup VALUES (3060149565)INSERT INTO @tblLookup VALUES (1994146192)INSERT INTO @tblLookup VALUES (31158534)INSERT INTO @tblLookup VALUES (2563907772)INSERT INTO @tblLookup VALUES (4023717930)INSERT INTO @tblLookup VALUES (1907459465)INSERT INTO @tblLookup VALUES (112637215)INSERT INTO @tblLookup VALUES (2680153253)INSERT INTO @tblLookup VALUES (3904427059)INSERT INTO @tblLookup VALUES (2013776290)INSERT INTO @tblLookup VALUES (251722036)INSERT INTO @tblLookup VALUES (2517215374)INSERT INTO @tblLookup VALUES (3775830040)INSERT INTO @tblLookup VALUES (2137656763)INSERT INTO @tblLookup VALUES (141376813)INSERT INTO @tblLookup VALUES (2439277719)INSERT INTO @tblLookup VALUES (3865271297)INSERT INTO @tblLookup VALUES (1802195444)INSERT INTO @tblLookup VALUES (476864866)INSERT INTO @tblLookup VALUES (2238001368)INSERT INTO @tblLookup VALUES (4066508878)INSERT INTO @tblLookup VALUES (1812370925)INSERT INTO @tblLookup VALUES (453092731)INSERT INTO @tblLookup VALUES (2181625025)INSERT INTO @tblLookup VALUES (4111451223)INSERT INTO @tblLookup VALUES (1706088902)INSERT INTO @tblLookup VALUES (314042704)INSERT INTO @tblLookup VALUES (2344532202)INSERT INTO @tblLookup VALUES (4240017532)INSERT INTO @tblLookup VALUES (1658658271)INSERT INTO @tblLookup VALUES (366619977)INSERT INTO @tblLookup VALUES (2362670323)INSERT INTO @tblLookup VALUES (4224994405)INSERT INTO @tblLookup VALUES (1303535960)INSERT INTO @tblLookup VALUES (984961486)INSERT INTO @tblLookup VALUES (2747007092)INSERT INTO @tblLookup VALUES (3569037538)INSERT INTO @tblLookup VALUES (1256170817)INSERT INTO @tblLookup VALUES (1037604311)INSERT INTO @tblLookup VALUES (2765210733)INSERT INTO @tblLookup VALUES (3554079995)INSERT INTO @tblLookup VALUES (1131014506)INSERT INTO @tblLookup VALUES (879679996)INSERT INTO @tblLookup VALUES (2909243462)INSERT INTO @tblLookup VALUES (3663771856)INSERT INTO @tblLookup VALUES (1141124467)INSERT INTO @tblLookup VALUES (855842277)INSERT INTO @tblLookup VALUES (2852801631)INSERT INTO @tblLookup VALUES (3708648649)INSERT INTO @tblLookup VALUES (1342533948)INSERT INTO @tblLookup VALUES (654459306)INSERT INTO @tblLookup VALUES (3188396048)INSERT INTO @tblLookup VALUES (3373015174)INSERT INTO @tblLookup VALUES (1466479909)INSERT INTO @tblLookup VALUES (544179635)INSERT INTO @tblLookup VALUES (3110523913)INSERT INTO @tblLookup VALUES (3462522015)INSERT INTO @tblLookup VALUES (1591671054)INSERT INTO @tblLookup VALUES (702138776)INSERT INTO @tblLookup VALUES (2966460450)INSERT INTO @tblLookup VALUES (3352799412)INSERT INTO @tblLookup VALUES (1504918807)INSERT INTO @tblLookup VALUES (783551873)INSERT INTO @tblLookup VALUES (3082640443)INSERT INTO @tblLookup VALUES (3233442989)INSERT INTO @tblLookup VALUES (3988292384)INSERT INTO @tblLookup VALUES (2596254646)INSERT INTO @tblLookup VALUES (62317068)INSERT INTO @tblLookup VALUES (1957810842)INSERT INTO @tblLookup VALUES (3939845945)INSERT INTO @tblLookup VALUES (2647816111)INSERT INTO @tblLookup VALUES (81470997)INSERT INTO @tblLookup VALUES (1943803523)INSERT INTO @tblLookup VALUES (3814918930)INSERT INTO @tblLookup VALUES (2489596804)INSERT INTO @tblLookup VALUES (225274430)INSERT INTO @tblLookup VALUES (2053790376)INSERT INTO @tblLookup VALUES (3826175755)INSERT INTO @tblLookup VALUES (2466906013)INSERT INTO @tblLookup VALUES (167816743)INSERT INTO @tblLookup VALUES (2097651377)INSERT INTO @tblLookup VALUES (4027552580)INSERT INTO @tblLookup VALUES (2265490386)INSERT INTO @tblLookup VALUES (503444072)INSERT INTO @tblLookup VALUES (1762050814)INSERT INTO @tblLookup VALUES (4150417245)INSERT INTO @tblLookup VALUES (2154129355)INSERT INTO @tblLookup VALUES (426522225)INSERT INTO @tblLookup VALUES (1852507879)INSERT INTO @tblLookup VALUES (4275313526)INSERT INTO @tblLookup VALUES (2312317920)INSERT INTO @tblLookup VALUES (282753626)INSERT INTO @tblLookup VALUES (1742555852)INSERT INTO @tblLookup VALUES (4189708143)INSERT INTO @tblLookup VALUES (2394877945)INSERT INTO @tblLookup VALUES (397917763)INSERT INTO @tblLookup VALUES (1622183637)INSERT INTO @tblLookup VALUES (3604390888)INSERT INTO @tblLookup VALUES (2714866558)INSERT INTO @tblLookup VALUES (953729732)INSERT INTO @tblLookup VALUES (1340076626)INSERT INTO @tblLookup VALUES (3518719985)INSERT INTO @tblLookup VALUES (2797360999)INSERT INTO @tblLookup VALUES (1068828381)INSERT INTO @tblLookup VALUES (1219638859)INSERT INTO @tblLookup VALUES (3624741850)INSERT INTO @tblLookup VALUES (2936675148)INSERT INTO @tblLookup VALUES (906185462)INSERT INTO @tblLookup VALUES (1090812512)INSERT INTO @tblLookup VALUES (3747672003)INSERT INTO @tblLookup VALUES (2825379669)INSERT INTO @tblLookup VALUES (829329135)INSERT INTO @tblLookup VALUES (1181335161)INSERT INTO @tblLookup VALUES (3412177804)INSERT INTO @tblLookup VALUES (3160834842)INSERT INTO @tblLookup VALUES (628085408)INSERT INTO @tblLookup VALUES (1382605366)INSERT INTO @tblLookup VALUES (3423369109)INSERT INTO @tblLookup VALUES (3138078467)INSERT INTO @tblLookup VALUES (570562233)INSERT INTO @tblLookup VALUES (1426400815)INSERT INTO @tblLookup VALUES (3317316542)INSERT INTO @tblLookup VALUES (2998733608)INSERT INTO @tblLookup VALUES (733239954)INSERT INTO @tblLookup VALUES (1555261956)INSERT INTO @tblLookup VALUES (3268935591)INSERT INTO @tblLookup VALUES (3050360625)INSERT INTO @tblLookup VALUES (752459403)INSERT INTO @tblLookup VALUES (1541320221)INSERT INTO @tblLookup VALUES (2607071920)INSERT INTO @tblLookup VALUES (3965973030)INSERT INTO @tblLookup VALUES (1969922972)INSERT INTO @tblLookup VALUES (40735498)INSERT INTO @tblLookup VALUES (2617837225)INSERT INTO @tblLookup VALUES (3943577151)INSERT INTO @tblLookup VALUES (1913087877)INSERT INTO @tblLookup VALUES (83908371)INSERT INTO @tblLookup VALUES (2512341634)INSERT INTO @tblLookup VALUES (3803740692)INSERT INTO @tblLookup VALUES (2075208622)INSERT INTO @tblLookup VALUES (213261112)INSERT INTO @tblLookup VALUES (2463272603)INSERT INTO @tblLookup VALUES (3855990285)INSERT INTO @tblLookup VALUES (2094854071)INSERT INTO @tblLookup VALUES (198958881)INSERT INTO @tblLookup VALUES (2262029012)INSERT INTO @tblLookup VALUES (4057260610)INSERT INTO @tblLookup VALUES (1759359992)INSERT INTO @tblLookup VALUES (534414190)INSERT INTO @tblLookup VALUES (2176718541)INSERT INTO @tblLookup VALUES (4139329115)INSERT INTO @tblLookup VALUES (1873836001)INSERT INTO @tblLookup VALUES (414664567)INSERT INTO @tblLookup VALUES (2282248934)INSERT INTO @tblLookup VALUES (4279200368)INSERT INTO @tblLookup VALUES (1711684554)INSERT INTO @tblLookup VALUES (285281116)INSERT INTO @tblLookup VALUES (2405801727)INSERT INTO @tblLookup VALUES (4167216745)INSERT INTO @tblLookup VALUES (1634467795)INSERT INTO @tblLookup VALUES (376229701)INSERT INTO @tblLookup VALUES (2685067896)INSERT INTO @tblLookup VALUES (3608007406)INSERT INTO @tblLookup VALUES (1308918612)INSERT INTO @tblLookup VALUES (956543938)INSERT INTO @tblLookup VALUES (2808555105)INSERT INTO @tblLookup VALUES (3495958263)INSERT INTO @tblLookup VALUES (1231636301)INSERT INTO @tblLookup VALUES (1047427035)INSERT INTO @tblLookup VALUES (2932959818)INSERT INTO @tblLookup VALUES (3654703836)INSERT INTO @tblLookup VALUES (1088359270)INSERT INTO @tblLookup VALUES (936918000)INSERT INTO @tblLookup VALUES (2847714899)INSERT INTO @tblLookup VALUES (3736837829)INSERT INTO @tblLookup VALUES (1202900863)INSERT INTO @tblLookup VALUES (817233897)INSERT INTO @tblLookup VALUES (3183342108)INSERT INTO @tblLookup VALUES (3401237130)INSERT INTO @tblLookup VALUES (1404277552)INSERT INTO @tblLookup VALUES (615818150)INSERT INTO @tblLookup VALUES (3134207493)INSERT INTO @tblLookup VALUES (3453421203)INSERT INTO @tblLookup VALUES (1423857449)INSERT INTO @tblLookup VALUES (601450431)INSERT INTO @tblLookup VALUES (3009837614)INSERT INTO @tblLookup VALUES (3294710456)INSERT INTO @tblLookup VALUES (1567103746)INSERT INTO @tblLookup VALUES (711928724)INSERT INTO @tblLookup VALUES (3020668471)INSERT INTO @tblLookup VALUES (3272380065)INSERT INTO @tblLookup VALUES (1510334235)INSERT INTO @tblLookup VALUES (755167117)DECLARE @crc BIGINT, @len INT, @i INT, @index INTDECLARE @tblval BIGINTSET @crc = 0xFFFFFFFFSET @len = LEN(@input)SET @i = 1WHILE @i <= @lenBEGIN SET @index = ((@crc & 0xff) ^ ASCII(SUBSTRING(@input, @i, 1))) SET @tblval = (SELECT Value FROM @tblLookup WHERE ID = @Index) SET @crc = (@crc / 256) ^ @tblval SET @i = @i + 1 ENDSET @crc = ~@crcSELECT @crc as CRC32, CONVERT(VARBINARY(4), @crc) as CRC32Hex
I shortened Andrew Rollings' script to 11 lines, so he really gets the credit. This will run in SQL 2008 or higher. If you set the variable values after the DECLARE, it will run in SQL 2005. In 2005 and up the character limit is 2048, in SQL 2000 it's something like 512 (I can't remember how many spt_values of type P there are in SQL 2000). But this could be modified if necessary.
DECLARE @input VARCHAR(50)SET @input = 'test'SET NOCOUNT ONDECLARE @crc bigint = 0xFFFFFFFF, @Lookup varbinary(2048) = 0x0000000077073096EE0E612C990951BA076DC419706AF48FE963A5359E6495A30EDB883279DCB8A4E0D5E91E97D2D98809B64C2B7EB17CBDE7B82D0790BF1D911DB710646AB020F2F3B9714884BE41DE1ADAD47D6DDDE4EBF4D4B55183D385C7136C9856646BA8C0FD62F97A8A65C9EC14015C4F63066CD9FA0F3D638D080DF53B6E20C84C69105ED56041E4A26771723C03E4D14B04D447D20D85FDA50AB56B35B5A8FA42B2986CDBBBC9D6ACBCF94032D86CE345DF5C75DCD60DCFABD13D5926D930AC51DE003AC8D75180BFD0611621B4F4B556B3C423CFBA9599B8BDA50F2802B89E5F058808C60CD9B2B10BE9242F6F7C8758684C11C1611DABB6662D3D76DC419001DB710698D220BCEFD5102A71B1858906B6B51F9FBFE4A5E8B8D4337807C9A20F00F9349609A88EE10E98187F6A0DBB086D3D2D91646C97E6635C016B6B51F41C6C6162856530D8F262004E6C0695ED1B01A57B8208F4C1F50FC45765B0D9C612B7E9508BBEB8EAFCB9887C62DD1DDF15DA2D498CD37CF3FBD44C654DB261583AB551CEA3BC0074D4BB30E24ADFA5413DD895D7A4D1C46DD3D6F4FB4369E96A346ED9FCAD678846DA60B8D044042D7333031DE5AA0A4C5FDD0D7CC95005713C270241AABE0B1010C90C20865768B525206F85B3B966D409CE61E49F5EDEF90E29D9C998B0D09822C7D7A8B459B33D172EB40D81B7BD5C3BC0BA6CADEDB883209ABFB3B603B6E20C74B1D29AEAD547399DD277AF04DB261573DC1683E3630B1294643B840D6D6A3E7A6A5AA8E40ECF0B9309FF9D0A00AE277D079EB1F00F93448708A3D21E01F2686906C2FEF762575D806567CB196C36716E6B06E7FED41B7689D32BE010DA7A5A67DD4ACCF9B9DF6F8EBEEFF917B7BE4360B08ED5D6D6A3E8A1D1937E38D8C2C44FDFF252D1BB67F1A6BC57673FB506DD48B2364BD80D2BDAAF0A1B4C36034AF641047A60DF60EFC3A867DF55316E8EEF4669BE79CB61B38CBC66831A256FD2A05268E236CC0C7795BB0B4703220216B95505262FC5BA3BBEB2BD0B282BB45A925CB36A04C2D7FFA7B5D0CF312CD99E8B5BDEAE1D9B64C2B0EC63F226756AA39C026D930A9C0906A9EB0E363F720767850500571395BF4A82E2B87A147BB12BAE0CB61B3892D28E9BE5D5BE0D7CDCEFB70BDBDF2186D3D2D4F1D4E24268DDB3F81FDA836E81BE16CDF6B9265B6FB077E118B7477788085AE6FF0F6A7066063BCA11010B5C8F659EFFF862AE69616BFFD3166CCF45A00AE278D70DD2EE4E0483543903B3C2A7672661D06016F74969474D3E6E77DBAED16A4AD9D65ADC40DF0B6637D83BF0A9BCAE53DEBB9EC547B2CF7F30B5FFE9BDBDF21CCABAC28A53B3933024B4A3A6BAD03605CDD7069354DE572923D967BFB3667A2EC4614AB85D681B022A6F2B94B40BBE37C30C8EA15A05DF1B2D02EF8D;SELECT @crc = (@crc / 256) ^ Substring(@Lookup, ((@crc & 0xFF) ^ Ascii(Substring(@input, V.Number, 1))) * 4 + 1, 4)FROM master.dbo.spt_values VWHERE V.type = 'P' AND V.number BETWEEN 1 AND Len(@input)SET @crc = ~@crc;SELECT @crc CRC32, Convert(VARBINARY(4), @crc) CRC32Hex;
For anyone that has a need for this in function form, I took the solution from Andrew Rollings and ErikE and put into a set of usable functions. I also provided a solution for Unicode characters as well in function NCRC32 below:
CREATE FUNCTION dbo.IndexTable( @FirstIndex bigint , @LastIndex bigint)RETURNS @Result table( Id BIGINT PRIMARY KEY)WITH SCHEMABINDINGAS/*************************************************************************************************** DESCRIPTION: Create an one column table of indexes starting with first specified index and ending with last specified index. INPUT PARAMETERS: @FirstIndex: First index to start the list of indexes with. @LastIndex: Last index to end the list of indexes with. RETURN VALUE: Table with list of specified indexes. EXAMPLES: SELECT * FROM dbo.IndexTable(1, 20) SELECT * FROM dbo.IndexTable(1, 16) SELECT * FROM dbo.IndexTable(1, 17) SELECT * FROM dbo.IndexTable(1, 18) SELECT * FROM dbo.IndexTable(1, 1) SELECT * FROM dbo.IndexTable(1, 0)***************************************************************************************************/BEGIN DECLARE @max bigint , @offset bigint ; IF @LastIndex IS NULL RETURN ; IF @FirstIndex IS NULL RETURN ; INSERT INTO @Result VALUES (@FirstIndex+0), (@FirstIndex+1), (@FirstIndex+2), (@FirstIndex+3), (@FirstIndex+4) , (@FirstIndex+5), (@FirstIndex+6), (@FirstIndex+7), (@FirstIndex+8), (@FirstIndex+9) ; SELECT @max= MAX(Id) FROM @Result ; WHILE @max < @LastIndex BEGIN SET @offset = (1 + @max - @FirstIndex) ; INSERT INTO @Result SELECT Id = Id + @offset FROM @Result WHERE Id <= (@LastIndex - @offset) ; SELECT @max= MAX(Id) FROM @Result ; END DELETE FROM @Result WHERE Id > @LastIndex ; RETURNENDGOCREATE FUNCTION dbo.CRC32calc/*************************************************************************************************** DESCRIPTION Add a byte value to a CRC calculation. INPUT PARAMETERS: @crc Current CRC value. @byteval Byte value to add to CRC value. RETURN VALUE: Resulting CRC with bytevalue added. USAGE: Used by functions dbo.CRC32 and dbo.NCRC32***************************************************************************************************/( @crc bigint, @byteval int)RETURNS bigintWITH SCHEMABINDINGASBEGIN DECLARE @Lookup varbinary(2048) = 0x0000000077073096EE0E612C990951BA076DC419706AF48FE963A5359E6495A30EDB883279DCB8A4E0D5E91E97D2D98809B64C2B7EB17CBDE7B82D0790BF1D911DB710646AB020F2F3B9714884BE41DE1ADAD47D6DDDE4EBF4D4B55183D385C7136C9856646BA8C0FD62F97A8A65C9EC14015C4F63066CD9FA0F3D638D080DF53B6E20C84C69105ED56041E4A26771723C03E4D14B04D447D20D85FDA50AB56B35B5A8FA42B2986CDBBBC9D6ACBCF94032D86CE345DF5C75DCD60DCFABD13D5926D930AC51DE003AC8D75180BFD0611621B4F4B556B3C423CFBA9599B8BDA50F2802B89E5F058808C60CD9B2B10BE9242F6F7C8758684C11C1611DABB6662D3D76DC419001DB710698D220BCEFD5102A71B1858906B6B51F9FBFE4A5E8B8D4337807C9A20F00F9349609A88EE10E98187F6A0DBB086D3D2D91646C97E6635C016B6B51F41C6C6162856530D8F262004E6C0695ED1B01A57B8208F4C1F50FC45765B0D9C612B7E9508BBEB8EAFCB9887C62DD1DDF15DA2D498CD37CF3FBD44C654DB261583AB551CEA3BC0074D4BB30E24ADFA5413DD895D7A4D1C46DD3D6F4FB4369E96A346ED9FCAD678846DA60B8D044042D7333031DE5AA0A4C5FDD0D7CC95005713C270241AABE0B1010C90C20865768B525206F85B3B966D409CE61E49F5EDEF90E29D9C998B0D09822C7D7A8B459B33D172EB40D81B7BD5C3BC0BA6CADEDB883209ABFB3B603B6E20C74B1D29AEAD547399DD277AF04DB261573DC1683E3630B1294643B840D6D6A3E7A6A5AA8E40ECF0B9309FF9D0A00AE277D079EB1F00F93448708A3D21E01F2686906C2FEF762575D806567CB196C36716E6B06E7FED41B7689D32BE010DA7A5A67DD4ACCF9B9DF6F8EBEEFF917B7BE4360B08ED5D6D6A3E8A1D1937E38D8C2C44FDFF252D1BB67F1A6BC57673FB506DD48B2364BD80D2BDAAF0A1B4C36034AF641047A60DF60EFC3A867DF55316E8EEF4669BE79CB61B38CBC66831A256FD2A05268E236CC0C7795BB0B4703220216B95505262FC5BA3BBEB2BD0B282BB45A925CB36A04C2D7FFA7B5D0CF312CD99E8B5BDEAE1D9B64C2B0EC63F226756AA39C026D930A9C0906A9EB0E363F720767850500571395BF4A82E2B87A147BB12BAE0CB61B3892D28E9BE5D5BE0D7CDCEFB70BDBDF2186D3D2D4F1D4E24268DDB3F81FDA836E81BE16CDF6B9265B6FB077E118B7477788085AE6FF0F6A7066063BCA11010B5C8F659EFFF862AE69616BFFD3166CCF45A00AE278D70DD2EE4E0483543903B3C2A7672661D06016F74969474D3E6E77DBAED16A4AD9D65ADC40DF0B6637D83BF0A9BCAE53DEBB9EC547B2CF7F30B5FFE9BDBDF21CCABAC28A53B3933024B4A3A6BAD03605CDD7069354DE572923D967BFB3667A2EC4614AB85D681B022A6F2B94B40BBE37C30C8EA15A05DF1B2D02EF8D ; RETURN (@crc / 256) ^ Substring(@Lookup, ((@crc & 0xFF) ^ @byteval) * 4 + 1, 4) ;ENDGOCREATE FUNCTION dbo.CRC32/*************************************************************************************************** DESCRIPTION Compute 32-bit CRC from an ASCII character array. INPUT PARAMETERS: @input ASCII text to compute CRC for. RETURN VALUE: Resulting 32-bit CRC value. EXAMPLES: SELECT t.input, csum = CHECKSUM(t.input), t.crc, crchex = CONVERT(VARBINARY(8), t.crc) FROM ( SELECT t.input, crc = dbo.CRC32(t.input) FROM ( SELECT input = 'test' UNION SELECT input = 'x' UNION SELECT input = '' UNION SELECT input = NULL UNION SELECT input = 'stop' UNION SELECT input = 'pots' UNION SELECT input = 'System.IO.Stream' UNION SELECT input = 'SYSTEM.IO.Stream' UNION SELECT input = 'Test.fqn.data' UNION SELECT input = 'Test.fqn.datax' ) AS t ) AS t***************************************************************************************************/( @input varchar(max))RETURNS intWITH SCHEMABINDINGASBEGIN DECLARE @crc bigint = 0xFFFFFFFF , @result int ; SELECT @crc = dbo.CRC32calc(@crc, Ascii(Substring(@input, v.id, 1))) FROM dbo.IndexTable(1, LEN(@input)) AS v ORDER BY v.Id ; SET @result = CONVERT(int, CONVERT(VARBINARY(4), ~@crc)) ; RETURN @result ;ENDGOCREATE FUNCTION dbo.NCRC32/*************************************************************************************************** DESCRIPTION Compute 32-bit CRC from a UNICODE character array. INPUT PARAMETERS: @input ASCII text to compute CRC for. RETURN VALUE: Resulting 32-bit CRC value. EXAMPLES: SELECT t.input, csum = CHECKSUM(t.input), t.crc, crchex = CONVERT(VARBINARY(8), t.crc) FROM ( SELECT t.input, crc = dbo.NCRC32(t.input) FROM ( SELECT input = N'test' UNION SELECT input = N'x' UNION SELECT input = N'' UNION SELECT input = NULL UNION SELECT input = 'stop' UNION SELECT input = 'pots' UNION SELECT input = N'System.IO.Stream' UNION SELECT input = N'SYSTEM.IO.Stream' UNION SELECT input = N'Test.fqn.data' UNION SELECT input = N'Test.fqn.datax' ) AS t ) AS t***************************************************************************************************/( @input nvarchar(max))RETURNS intWITH SCHEMABINDINGASBEGIN DECLARE @crc bigint = 0xFFFFFFFF , @result int ; SELECT @crc = dbo.CRC32calc( dbo.CRC32calc(@crc, (cval / 256)), cval & 0xFF) FROM ( SELECT v.id, cval = UNICODE(SUBSTRING(@input, v.id, 1)) FROM dbo.IndexTable(1, LEN(@input)) AS v ) AS t ORDER BY t.Id ; SET @result = CONVERT(int, CONVERT(VARBINARY(4), ~@crc)) ; RETURN @result ;ENDGO