April 22, 2016 at 7:46 am
I've been trying to create a string manipulation functions natively compiled.
I am sure we all have had to write a garbage function with while loops to format a string to proper case, and I have done so with a CLR as well, and have the assembly.
I was hoping that we could do this with a natively compiled function, to reduce the need for creating assemblies and enabling CLR usage, and hoped we could have a faster proper case function.
Unfortunately, the parser doesn't like upper, lower and stuff.
Anyone have an idea how to create a propercase function natively compiled without using the functions as below?
** the code is not mine, just something I grabbed off the net somewhere.
CREATE FUNCTION dbo.ProperCaseHekaton (@InputString VARCHAR(8000))
RETURNS VARCHAR(8000)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
DECLARE @index INT
DECLARE @Char CHAR(1)
DECLARE @OutputString VARCHAR(255)
SET @OutputString = LOWER(@InputString)
SET @index = 2
SET @OutputString = STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))
WHILE @index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @index, 1)
IF @Char IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&','''','(')
IF @index + 1 <= LEN(@InputString)
BEGIN
IF @Char != ''''
OR
UPPER(SUBSTRING(@InputString, @index + 1, 1)) != 'S'
SET @OutputString =
STUFF(@OutputString, @index + 1, 1,UPPER(SUBSTRING(@InputString, @index + 1, 1)))
END
END
RETURN ISNULL(@OutputString,'');
END
GO
Msg 10794, Level 16, State 95, Procedure ProperCaseHekaton, Line 11 [Batch Start Line 48]
The function 'lower' is not supported with natively compiled modules.
Msg 10794, Level 16, State 95, Procedure ProperCaseHekaton, Line 12 [Batch Start Line 48]
The function 'upper' is not supported with natively compiled modules.
Msg 10794, Level 16, State 95, Procedure ProperCaseHekaton, Line 14 [Batch Start Line 48]
The function 'stuff' is not supported with natively compiled modules.
Msg 10794, Level 16, State 95, Procedure ProperCaseHekaton, Line 22 [Batch Start Line 48]
The function 'upper' is not supported with natively compiled modules.
Msg 10794, Level 16, State 95, Procedure ProperCaseHekaton, Line 24 [Batch Start Line 48]
The function 'upper' is not supported with natively compiled modules.
Msg 10794, Level 16, State 95, Procedure ProperCaseHekaton, Line 25 [Batch Start Line 48]
The function 'stuff' is not supported with natively compiled modules.
April 23, 2016 at 1:28 pm
I tried to build a "sort-of" substitute based on the ASCII and CHAR functions, but ran into the same issue you had: both ASCII and CHAR are not supported.
The list of features currently supported in natively compiled code is here: https://msdn.microsoft.com/en-us/library/dn452279%28v=sql.130%29.aspx. For string functions, the list of pretty short. And exactly equal to the list of string functions that was already supported in natively compiled code in SQL Server 2014.
Let's hope that there will be some additional goodies to be released after CTP3, because this is a very limited subset. And you are right that all those nasty string manipulations, which of course should never be done in the database (in theory), can take up a lot of cycles, so they would be a good candidate for native compilation. I'm pretty sure we all have a few functions on our servers that we'd love to make natively compiled as a first performance gain while we keep pushing the developers to make smarter choices.
April 25, 2016 at 4:35 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply