This article was about ufn_Rot13 a letter exchange cipher contributed by Luke Schollmeyer. The Rot13 cipher is often used for very basic hiding of text. The Windows Registry uses it and it’s also used in many web cookies.
ufn_Rot13 uses a loop to compute each letter in the output. This technique isn’t that unusual but it uses a relatively large amount of SQL Server’s computational capability. At least compared to the requirements of performing data manipulation alone.
After Luke sent the original ufn_Rot13 script, he sent a revised version that doesn’t use an explicit loop. Instead, it uses a table and gets the SQL engine to perform the loop in a select statement. This provides a good contrast to the plain loop and demonstrates one way of speeding up UDFs.
Let’s start with the table creation script:
number int
)
GOdeclare @i int
set @i = 0
while @i <= 8000 begin
insert numbers values(@i)
set @i = @i + 1
end
GO
Now we can create the revised UDF. Here’s the CREATE FUNCTION script:
GO
SET ANSI_NULLS ON
GOcreate function ufn_Rot13Redux (@input varchar(8000))
returns varchar(8000)
as
begin
declare @pass varchar(8000)
set @pass = ”
select
@pass = @pass +
CASE
WHEN ASCII(SUBSTRING(@input,n.number+1,1)) BETWEEN 97 AND 122
THEN CASE WHEN ASCII(SUBSTRING(@input,n.number+1,1)) – 13 < 97
THEN char((ASCII(SUBSTRING(@input,n.number+1,1)) – 13)
+ 122 – 96)
ELSE char(ASCII(SUBSTRING(@input,n.number+1,1)) – 13) END
WHEN ASCII(SUBSTRING(@input,n.number+1,1)) BETWEEN 65 AND 90
THEN CASE WHEN ASCII(SUBSTRING(@input,n.number+1,1)) – 13 < 65
THEN char((ASCII(SUBSTRING(@input,n.number+1,1)) – 13)
+ 90 – 64)
ELSE char(ASCII(SUBSTRING(@input,n.number+1,1)) – 13) END
ELSE CHAR(ASCII(SUBSTRING(@input,n.number+1,1)))
END
from
numbers n
where
n.number < len(@input)
return @pass
end
go
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXEC ON dbo.ufn_Rot13Redux to PUBLIC
GO
Let’s use the same simple phrase as last time for a basic test. The script illustrates a test of translating a phrase from plain text to encrypted text and back to a decrypted message:
, @CryptoText varchar(8000) — Encrypted text
, @Decrypted varchar(8000) — After decryptionSELECT @PlainText = ‘We are ever true to Brown’
SELECT @CryptoText = dbo.ufn_rot13redux(@PlainText)
SELECT @Decrypted = dbo.ufn_rot13redux(@Cryptotext)
SELECT CASE WHEN @PlainText = @Decrypted
THEN ‘Equal’ ELSE ‘Not equal’ END [Test]
, @CryptoText [Encrypted]
, @Decrypted [Decrypted]
go
Results:
| Test | Encrypted | Decrypted |
|---|---|---|
| Equal | Jr ner rire gehr gb Oebja | We are ever true to Brown |
Luke used sort of a SQL trick in the SELECT statement:
Since the SELECT statement is executed for every row it gets executed for every character in the input string and the result is concatenated to @Pass. Of course that means that there are as many concatenation operations as there are letters in the input but there were just as many in the original version with the WHILE loop. Of course, this time they’re all being executed in the SELECT statement.
Due to the need for a lot of setup for the test, I’m going to postpone a performance comparison between the type versions of the ROT13 algorithm for another article.