11

Does SQL Server CheckSum calculate a CRC? If not how can I get SQL Server to calculate a CRC on an arbitrary varchar column?

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Brian R. Bondy
  • 339,232
  • 124
  • 596
  • 636

7 Answers7

5

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 ON
DECLARE
    @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 V
WHERE V.type = 'P' AND V.number BETWEEN 1 AND Len(@input)

SET @crc = ~@crc;
SELECT @crc CRC32, Convert(VARBINARY(4), @crc) CRC32Hex;
ErikE
  • 48,881
  • 23
  • 151
  • 196
  • Hmmm, some further analysis suggests that I should review whether the CRC is truly calculated in the correct order. If the rows from `master.dbo.spt_values` come in the wrong order, the CRC will be wrong. – ErikE Mar 27 '13 at 17:16
5

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 ON
DECLARE @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 INT
DECLARE @tblval BIGINT
SET @crc = 0xFFFFFFFF
SET @len = LEN(@input)
SET @i = 1

WHILE @i <= @len
BEGIN
    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 
END
SET @crc = ~@crc

SELECT @crc as CRC32, CONVERT(VARBINARY(4), @crc) as CRC32Hex
Andrew Rollings
  • 14,340
  • 7
  • 51
  • 50
  • I forgot to put in the edit comment, but I added the "AS CRC32" to the last line of the TSQL script to make it name the selected column. – Andrew Rollings Dec 01 '08 at 17:17
  • I would explicitly put the index values in your lookup table instead of relying on IDENTITY. Your method should work 100% of the time since the table is local, but it's not guaranteed that IDENTITY creates seq values. Mostly just a habit of mine though. If you know the value, might as well put it in – Tom H Dec 01 '08 at 19:08
  • Hmm... Didn't know that. The TSQL docs seem to indicate otherwise: "IDENTITY [ (seed , increment) ] seed: Is the value that is used for the very first row loaded into the table increment: Is the incremental value that is added to the identity value of the previous row that was loaded. " – Andrew Rollings Dec 01 '08 at 19:13
  • @Andrew: If an insert gets rolled back, the values inserted would contain a gap. There are other reasons why a gap might occur as well, but none of them really apply in your case. Like I said, in your code you should be safe. I state values when I know them (including incrementing #s) out of habit. – Tom H Dec 01 '08 at 20:00
  • I stand (well sit, actually) corrected. I wouldn't set up the lookup as a table variable like that... I just wanted to create a good piece of example code that wouldn't do anything permanent to someone's database :) There is probably a better solution that doesn't use the table variable at all. – Andrew Rollings Dec 01 '08 at 23:00
  • 1
    @Andrew see [my answer](http://stackoverflow.com/a/11043513/57611) for a shortened version. – ErikE Jun 15 '12 at 01:25
2

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 SCHEMABINDING
AS
/***************************************************************************************************
    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
    ;
    RETURN
END
GO

CREATE 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 bigint
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Lookup     varbinary(2048) = 0x0000000077073096EE0E612C990951BA076DC419706AF48FE963A5359E6495A30EDB883279DCB8A4E0D5E91E97D2D98809B64C2B7EB17CBDE7B82D0790BF1D911DB710646AB020F2F3B9714884BE41DE1ADAD47D6DDDE4EBF4D4B55183D385C7136C9856646BA8C0FD62F97A8A65C9EC14015C4F63066CD9FA0F3D638D080DF53B6E20C84C69105ED56041E4A26771723C03E4D14B04D447D20D85FDA50AB56B35B5A8FA42B2986CDBBBC9D6ACBCF94032D86CE345DF5C75DCD60DCFABD13D5926D930AC51DE003AC8D75180BFD0611621B4F4B556B3C423CFBA9599B8BDA50F2802B89E5F058808C60CD9B2B10BE9242F6F7C8758684C11C1611DABB6662D3D76DC419001DB710698D220BCEFD5102A71B1858906B6B51F9FBFE4A5E8B8D4337807C9A20F00F9349609A88EE10E98187F6A0DBB086D3D2D91646C97E6635C016B6B51F41C6C6162856530D8F262004E6C0695ED1B01A57B8208F4C1F50FC45765B0D9C612B7E9508BBEB8EAFCB9887C62DD1DDF15DA2D498CD37CF3FBD44C654DB261583AB551CEA3BC0074D4BB30E24ADFA5413DD895D7A4D1C46DD3D6F4FB4369E96A346ED9FCAD678846DA60B8D044042D7333031DE5AA0A4C5FDD0D7CC95005713C270241AABE0B1010C90C20865768B525206F85B3B966D409CE61E49F5EDEF90E29D9C998B0D09822C7D7A8B459B33D172EB40D81B7BD5C3BC0BA6CADEDB883209ABFB3B603B6E20C74B1D29AEAD547399DD277AF04DB261573DC1683E3630B1294643B840D6D6A3E7A6A5AA8E40ECF0B9309FF9D0A00AE277D079EB1F00F93448708A3D21E01F2686906C2FEF762575D806567CB196C36716E6B06E7FED41B7689D32BE010DA7A5A67DD4ACCF9B9DF6F8EBEEFF917B7BE4360B08ED5D6D6A3E8A1D1937E38D8C2C44FDFF252D1BB67F1A6BC57673FB506DD48B2364BD80D2BDAAF0A1B4C36034AF641047A60DF60EFC3A867DF55316E8EEF4669BE79CB61B38CBC66831A256FD2A05268E236CC0C7795BB0B4703220216B95505262FC5BA3BBEB2BD0B282BB45A925CB36A04C2D7FFA7B5D0CF312CD99E8B5BDEAE1D9B64C2B0EC63F226756AA39C026D930A9C0906A9EB0E363F720767850500571395BF4A82E2B87A147BB12BAE0CB61B3892D28E9BE5D5BE0D7CDCEFB70BDBDF2186D3D2D4F1D4E24268DDB3F81FDA836E81BE16CDF6B9265B6FB077E118B7477788085AE6FF0F6A7066063BCA11010B5C8F659EFFF862AE69616BFFD3166CCF45A00AE278D70DD2EE4E0483543903B3C2A7672661D06016F74969474D3E6E77DBAED16A4AD9D65ADC40DF0B6637D83BF0A9BCAE53DEBB9EC547B2CF7F30B5FFE9BDBDF21CCABAC28A53B3933024B4A3A6BAD03605CDD7069354DE572923D967BFB3667A2EC4614AB85D681B022A6F2B94B40BBE37C30C8EA15A05DF1B2D02EF8D
    ;
    RETURN (@crc / 256) ^ Substring(@Lookup, ((@crc & 0xFF) ^ @byteval) * 4 + 1, 4)
    ;
END
GO

CREATE 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 int
WITH SCHEMABINDING
AS
BEGIN
    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 ;
END
GO

CREATE 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 int
WITH SCHEMABINDING
AS
BEGIN
    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 ;
END
GO
Michael Erickson
  • 4,217
  • 2
  • 23
  • 10
1

Here's a non-table-based solution derived from the original code post above. The experts will have to weigh in whether this is or isn't more efficient than the table-based solution. My thought is that if you compute the checksum of 1,000 records with a 1-row character count of 1,000 characters, you're performing 1,000,000 select hits to your CRC lookup table, and that just can't be more efficient than in-memory computation. Maybe it is. 

Declare @input as varchar(1000) 
Set @input='This is the CRC test' 
Declare @CRCtable as varchar(3080)   --Location of Edit
Declare @Index as int  
Declare @crc as BIGINT 
Declare @length as INT 
Declare @i as INT 
Declare @tblval as BIGINT  
Declare @CTindex as int 
Declare @ans as varchar(25) 

Set @CRCtable='0000000000, 1996959894, 3993919788, 2567524794, 0124634137, 1886057615, 3915621685, 2657392035, 0249268274, 2044508324, 3772115230, 2547177864, 0162941995, 2125561021, 3887607047, 2428444049, 0498536548, 1789927666, 4089016648, 2227061214, 0450548861, 1843258603, 4107580753, 2211677639, 0325883990, 1684777152, 4251122042, 2321926636, 0335633487, 1661365465, 4195302755, 2366115317, 0997073096, 1281953886, 3579855332, 2724688242, 1006888145, 1258607687, 3524101629, 2768942443, 0901097722, 1119000684, 3686517206, 2898065728, 0853044451, 1172266101, 3705015759, 2882616665, 0651767980, 1373503546, 3369554304, 3218104598, 0565507253, 1454621731, 3485111705, 3099436303, 0671266974, 1594198024, 3322730930, 2970347812, 0795835527, 1483230225, 3244367275, 3060149565, 1994146192, 0031158534, 2563907772, 4023717930, 1907459465, 0112637215, 2680153253, 3904427059, 2013776290, 0251722036, 2517215374, 3775830040, 2137656763, 0141376813, 2439277719, 3865271297, 1802195444, 0476864866, 2238001368, 4066508878, 1812370925, 0453092731, 2181625025, 4111451223, 1706088902, 0314042704, 2344532202, 4240017532, 1658658271, 0366619977, 2362670323, 4224994405, 1303535960, 0984961486, 2747007092, 3569037538, 1256170817, 1037604311, 2765210733, 3554079995, 1131014506, 0879679996, 2909243462, 3663771856, 1141124467, 0855842277, 2852801631, 3708648649, 1342533948, 0654459306, 3188396048, 3373015174, 1466479909, 0544179635, 3110523913, 3462522015, 1591671054, 0702138776, 2966460450, 3352799412, 1504918807, 0783551873, 3082640443, 3233442989, 3988292384, 2596254646, 0062317068, 1957810842, 3939845945, 2647816111, 0081470997, 1943803523, 3814918930, 2489596804, 0225274430, 2053790376, 3826175755, 2466906013, 0167816743, 2097651377, 4027552580, 2265490386, 0503444072, 1762050814, 4150417245, 2154129355, 0426522225, 1852507879, 4275313526, 2312317920, 0282753626, 1742555852, 4189708143, 2394877945, 0397917763, 1622183637, 3604390888, 2714866558, 0953729732, 1340076626, 3518719985, 2797360999, 1068828381, 1219638859, 3624741850, 2936675148, 0906185462, 1090812512, 3747672003, 2825379669, 0829329135, 1181335161, 3412177804, 3160834842, 0628085408, 1382605366, 3423369109, 3138078467, 0570562233, 1426400815, 3317316542, 2998733608, 0733239954, 1555261956, 3268935591, 3050360625, 0752459403, 1541320221, 2607071920, 3965973030, 1969922972, 0040735498, 2617837225, 3943577151, 1913087877, 0083908371, 2512341634, 3803740692, 2075208622, 0213261112, 2463272603, 3855990285, 2094854071, 0198958881, 2262029012, 4057260610, 1759359992, 0534414190, 2176718541, 4139329115, 1873836001, 0414664567, 2282248934, 4279200368, 1711684554, 0285281116, 2405801727, 4167216745, 1634467795, 0376229701, 2685067896, 3608007406, 1308918612, 0956543938, 2808555105, 3495958263, 1231636301, 1047427035, 2932959818, 3654703836, 1088359270, 0936918000, 2847714899, 3736837829, 1202900863, 0817233897, 3183342108, 3401237130, 1404277552, 0615818150, 3134207493, 3453421203, 1423857449, 0601450431, 3009837614, 3294710456, 1567103746, 0711928724, 3020668471, 3272380065, 1510334235, 0755167117, '
Set @crc = 0xFFFFFFFF  
Set @length = LEN(@input)  
Set @i = 1  

While @i <= @length  
  Begin  
  Set @index = ((@crc & 0xff) ^ ASCII(SUBSTRING(@input, @i, 1)))        
  Set @CTindex = (@index * 12) + 1
  Set @ans=substring(@CRCtable,@CTindex,10  )  
  Set @tblval = convert(bigint,@ans) 
  Set @crc = (@crc / 256) ^ @tblval     
  Set @i = @i + 1       
  End  
Set @crc = ~@crc  

SELECT @crc as CRC32, CONVERT(VARBINARY(4), @crc) as CRC32Hex
Jim M
  • 541
  • 1
  • 6
  • 18
  • Unfortunately this doesn't always give the correct result. This string doesn't work "schemenewbusiness/ACM0025/S100/default.aspx". – Dale K Feb 18 '12 at 04:16
  • Found it, @CRCtable needs to be varchar(3080) – Dale K Feb 18 '12 at 04:38
  • 1
    Why use delimiters when you don't need them? And why use string at all? String conversion and manipulation are very slow compared to number data types. – ErikE Jun 14 '12 at 23:43
  • Also, the lookup table was a table variable, so probably would fit in memory. But see my answer now. – ErikE Jun 15 '12 at 01:27
1

I don't think T-SQL gives you the option to specify using a proper CRC as your checksum function. FYI, SQL Server Integration Services (SSIS) does have a "Checksum" transformation that gives you a choice of what checksum algorithm to use, and one of the choices is:

CRC32 - Using a standard 32-bit cyclic redundancy check (CRC), this provides a more open implementation.

To implement a CRC in SQL, you can either write a pure T-SQL implementation of CRC (fun!), or (if you're using SQL Server 2005 or higher) write / find it in another .NET language and then use that class as a compiled SQL stored procedure.

Ian Varley
  • 9,227
  • 5
  • 29
  • 34
1

Is this an educational exercise, or do you need to actually use it in an application. If the second, I recommend you do it in the application rather than SQL Server. Any of the available methods, even using the CLR, will probably be more work and riskier if there is an alternative. And most options can affect SS performance.

If you need it for input validation, then the module that loads it should check while loading (and maybe set a status column.) If you need it for output and it's not easy to calculate it on the fly, then let the input module calculate it and store it in a column.

If you want to use it to verify that the column hasn't been corrupted, then there are better ways to deal with a problem that is either unlikely, or you have an unstable server that needs to be dealt with.

dkretz
  • 37,399
  • 13
  • 80
  • 138
1

SQL 2005 and after has the functions CHECKSUM and CHECKSUM_AGG. This is similar to a CRC and may work for hash tables or quickly check for changed data. CHECKSUM(*) will return a value that includes all the columns for a single row.

I briefly tested it and it is not sensitive to case and trailing blanks on strings. It does change if with a leading or embedded blank. Order of values is important.

Echilon
  • 10,064
  • 33
  • 131
  • 217
Ken
  • 11
  • 1