SQL Server CheckSum是否计算CRC？如果不是，我怎样才能让MS SQL计算任意varchar列的CRC？

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

SQL Server CheckSum是否计算CRC？如果不是，我如何让SQL Server计算任意varchar列上的CRC？

7 个解决方案

#1

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...

我为模型的粗糙道歉，但这似乎做了正确的CRC32计算。我不是TSQL专家，我确信这可以通过真正的SQL Server专业人员大大改善...

@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).

@input是计算CRC32的变量。将它打包为sproc或udf应该是微不足道的，并且查找表可以被分解为永久表（甚至可以动态计算）。

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.

编辑：我已经检查了我对http://crc32-checksum.waraxe.us/的结果，到目前为止似乎很好。

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

#2

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.

我将Andrew Rollings的剧本简化为11行，所以他真的得到了信任。这将在SQL 2008或更高版本中运行。如果在DECLARE之后设置变量值，它将在SQL 2005中运行。在2005年及以上字符限制为2048，在SQL 2000中它类似于512（我不记得SQL中有多少个P类型的spt_values 2000）。但如有必要，可以对此进行修改。

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;

#3

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:

对于任何需要功能形式的人来说，我从Andrew Rollings和ErikE那里获得了解决方案，并提供了一组可用的功能。我还在下面的函数NCRC32中提供了Unicode字符的解决方案：

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

#4

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:

我不认为T-SQL允许您选择使用适当的CRC作为校验和函数。仅供参考，SQL Server Integration Services（SSIS）确实具有“校验和”转换，可让您选择要使用的校验和算法，其中一个选项是：

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

CRC32 - 使用标准的32位循环冗余校验（CRC），这提供了更开放的实现。

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.

要在SQL中实现CRC，您可以编写CRC的纯T-SQL实现（好玩！），或者（如果您使用的是SQL Server 2005或更高版本）用另一种.NET语言编写/查找它，然后使用它class作为已编译的SQL存储过程。

#5

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.

这是一项教育练习，还是需要在应用程序中实际使用它。如果第二个，我建议你在应用程序而不是SQL Server中进行。任何可用的方法，即使使用CLR，如果有替代方案，可能会有更多的工作和风险。大多数选项都会影响SS性能。

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.

如果您想使用它来验证列是否已损坏，那么有更好的方法来处理不太可能发生的问题，或者您有一个需要处理的不稳定服务器。

#6

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.

这是从上面的原始代码帖子派生的非基于表的解决方案。专家们将不得不权衡这是否比基于表格的解决方案更有效。我的想法是，如果你计算1000行记录的1行字符数为1,000个字符的校验和，那么你在CRC查找表中执行1,000,000次选择命中，这就不能比内存计算更有效。也许是。

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

#7

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.

SQL 2005及之后具有CHECKSUM和CHECKSUM_AGG函数。这类似于CRC，可以用于哈希表或快速检查已更改的数据。 CHECKSUM（*）将返回一个包含单行所有列的值。

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.

我对它进行了简单测试，它对字符串上的大小写和尾随空白不敏感。如果使用前导或嵌入空白，它确实会发生变化。价值秩序很重要。

#1