Does SQL Server CheckSum calculate a CRC? If not how can I get SQL Server to calculate a CRC on an arbitrary varchar column?
views:
1881answers:
5
A:
I belive there is a crc32() function built in. If not, you may have to do it at the application level.
Eli
2008-12-01 15:29:08
no there is no such function for me
Brian R. Bondy
2008-12-01 15:40:14
+1
A:
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
2008-12-01 15:57:21
+3
A:
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
2008-12-01 16:50:30
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
2008-12-01 17:17:19
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.
2008-12-01 19:08:00
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 tableincrement: Is the incremental value that is added to the identity value of the previous row that was loaded."
Andrew Rollings
2008-12-01 19:13:05
@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.
2008-12-01 20:00:32
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
2008-12-01 23:00:49
+1
A:
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.
le dorfier
2008-12-01 17:12:05
A:
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(3000)
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
2010-06-15 19:16:44