Efficient calculation of an ISBN-13 check digit
I've been working in and around SQL for over 3 decades.
I am currently working on making our business intelligence systems self-driven by using an event-based architecture. This involves providing curated data in some of the largest SQL Azure Analysis Services cubes ever built (over 6 billion records in 1 table alone).
I've developed several APIs leveraging a serverless Azure architecture to provide real-time data to various internal processes and projects.
Currently working on Project Dormouse; Durable select messaging queue that enables polling processes to extract specific messages.
I thought I might pass along, what I have found to be, the most efficient way to validate the check digit within Azure SQL Server. I was looking to go down the path of a CLR, but it turns out that seems to be frowned upon. The function returns Y/N. For my environment, which is a 24CPU HyperScale, I can process approximately 50,000 isbns/second. My test involves reading ISBNs from a table and outputting the value into a temp table.
CREATE function [dbo].[ValidateISBN]
(@ISBN as bigint)
returns char(1)
as
begin
declare @is978 tinyint=(978-(@ISBN /10000000000))*-1,
@checkdigit tinyint
set @checkdigit =10-cast((cast((@ISBN %10000000000000/1000000000000) as tinyint)
+ cast((@ISBN %1000000000000/100000000000) as tinyint)*3
+ cast((@ISBN %100000000000/10000000000) as tinyint)
+ cast((@ISBN %10000000000/1000000000) as tinyint)*3
+ cast((@ISBN %1000000000/100000000) as tinyint)
+ cast((@ISBN %100000000/10000000) as tinyint)*3
+ cast((@ISBN %10000000/1000000) as tinyint)
+ cast((@ISBN %1000000/100000) as tinyint)*3
+ cast((@ISBN %100000/10000) as tinyint)
+ cast((@ISBN %10000/1000) as tinyint)*3
+ cast((@ISBN %1000/100) as tinyint)
+ cast((@ISBN %100/10) as tinyint)*3)as tinyint)%10
if ((@checkdigit=10 and @ISBN %10=0) or @checkdigit=@ISBN %10)
return 'Y'
return 'N'
end
GO
