uso - ¿SQL Server CheckSum calcula un CRC? Si no, ¿cómo puedo obtener MS SQL para calcular un CRC en una columna varchar arbitraria?

update output sql server 2008

Question

¿SQL Server CheckSum calcula un CRC? Si no, ¿cómo puedo hacer que SQL Server calcule un CRC en una columna varchar arbitraria?

Answer 1

Acorté el guión de Andrew Rollings a 11 líneas, por lo que realmente obtiene el crédito. Esto se ejecutará en SQL 2008 o superior. Si establece los valores de las variables después de DECLARAR, se ejecutará en SQL 2005. En 2005 y hasta el límite de caracteres es 2048, en SQL 2000 es algo así como 512 (no recuerdo cuántos valores de spt de tipo P hay en SQL 2000). Pero esto podría modificarse si es necesario.

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;

Answer 2

¿Es este un ejercicio educativo, o necesita usarlo en una aplicación? Si es el segundo, te recomiendo que lo hagas en la aplicación en lugar de SQL Server. Cualquiera de los métodos disponibles, incluso usando el CLR, probablemente será más trabajo y más arriesgado si hay una alternativa. Y la mayoría de las opciones pueden afectar el rendimiento de SS.

Si lo necesita para la validación de entrada, entonces el módulo que lo carga debe verificar durante la carga (y quizás establecer una columna de estado.) Si lo necesita para la salida y no es fácil calcularlo sobre la marcha, deje que el módulo de entrada calcule y almacenarlo en una columna.

Si desea utilizarlo para verificar que la columna no se haya corrompido, existen mejores formas de resolver un problema que no es probable o que tiene que tratar con un servidor inestable.

Answer 3

Me disculpo por la crudeza del modelo, pero esto parece hacer un cálculo correcto de CRC32. No soy un experto en TSQL, y estoy seguro de que esto podría ser mejorado poderosamente por un profesional real de SQL Server ...

@input es la variable para calcular el CRC32 en. Debería ser trivial empaquetar esto como un sproc o un udf, y la tabla de búsqueda podría tenerse en cuenta en una tabla permanente (o incluso calculada sobre la marcha).

De todos modos, parece funcionar. Me gustaría ver alguna mejora, ya que siempre es bueno aprender nuevos trucos :)

EDITAR: He comprobado mis resultados en contra de http://crc32-checksum.waraxe.us/ y parece bueno hasta ahora.

Andrés

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

Answer 4

No creo que T-SQL le brinde la opción de especificar el uso de un CRC adecuado como su función de suma de comprobación. Para su información, SQL Server Integration Services (SSIS) tiene una transformación "Checksum" que le permite elegir qué algoritmo de suma de control usar, y una de las opciones es:

CRC32: utilizando una comprobación de redundancia cíclica (CRC) estándar de 32 bits, esto proporciona una implementación más abierta.

Para implementar un CRC en SQL, puede escribir una implementación pura de T-SQL de CRC (¡diversión!), O (si usa SQL Server 2005 o superior) escribir / buscar en otro lenguaje .NET y luego usar eso clase como un procedimiento almacenado SQL compilado.

Answer 5

Aquí hay una solución no basada en tablas derivada de la publicación del código original anterior. Los expertos tendrán que sopesar si esto es o no es más eficiente que la solución basada en tablas. Mi opinión es que si calculas la suma de comprobación de 1,000 registros con un conteo de 1 fila de caracteres de 1,000 caracteres, estás realizando 1,000,000 de visitas selectas a tu tabla de búsqueda de CRC, y eso simplemente no puede ser más eficiente que el cálculo en memoria . Tal vez lo sea

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

Answer 6

SQL 2005 y después tiene las funciones CHECKSUM y CHECKSUM_AGG . Esto es similar a un CRC y puede funcionar para tablas hash o comprobar rápidamente los datos modificados. CHECKSUM(*) devolverá un valor que incluye todas las columnas de una sola fila.

Lo probé brevemente y no es sensible a los espacios en blanco y finales en las cadenas. Cambia si con un espacio en blanco inicial o incrustado. El orden de los valores es importante.

Answer 7

Para cualquiera que tenga una necesidad de esto en forma de función, tomé la solución de Andrew Rollings y ErikE y la puse en un conjunto de funciones utilizables. También proporcioné una solución para caracteres Unicode también en la función NCRC32 a continuación:

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