do you have a StripNonNumeric ITVF function?

Question

Post reply

do you have a StripNonNumeric ITVF function?

Viewing 15 posts - 16 through 30 (of 45 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 1

Little clean up in the logic, now it looks pretty good

😎

/********************************************************************

-- Stripping out any non-numerical characters

-- EE 2014-10-28 Inital coding

-- EE Cleaned up comparison logic

********************************************************************/

ALTER FUNCTION dbo.STRIP_NUM_EE

(

@INSTR VARCHAR(8000)

)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

,NUMS(N) AS (SELECT TOP(CONVERT(BIGINT,LEN(@INSTR),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4)

SELECT

(

SELECT

CASE WHEN (ASCII(SUBSTRING(@INSTR COLLATE Latin1_General_BIN,NM.N,1)) - 48) BETWEEN 0 AND 9 THEN SUBSTRING(@INSTR,NM.N,1) END

FROM NUMS NM

FOR XML PATH('')

) AS OUT_STR

;

Test results

Beginning execution loop

========== Using nGrams ==========

1666

========== Using nGrams ==========

1680

========== Using nGrams ==========

1706

Batch execution completed 3 times.

Beginning execution loop

========== Using PatExclude8K ==========

2830

========== Using PatExclude8K ==========

2756

========== Using PatExclude8K ==========

2750

Batch execution completed 3 times.

Beginning execution loop

========== Jeff's Old Scalar Loop Function ==========

1596

========== Jeff's Old Scalar Loop Function ==========

1556

========== Jeff's Old Scalar Loop Function ==========

1556

Batch execution completed 3 times.

Beginning execution loop

========== dbo.STRIP_NUM_EE Function ==========

1393

========== dbo.STRIP_NUM_EE Function ==========

1343

========== dbo.STRIP_NUM_EE Function ==========

1343

Batch execution completed 3 times.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

Eirikur Eiriksson (10/28/2014)
Little clean up in the logic, now it looks pretty good
😎
/********************************************************************
-- Stripping out any non-numerical characters
-- EE 2014-10-28 Inital coding
-- EE Cleaned up comparison logic
********************************************************************/
ALTER FUNCTION dbo.STRIP_NUM_EE
(
@INSTR VARCHAR(8000)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(CONVERT(BIGINT,LEN(@INSTR),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4)
SELECT
(
SELECT
CASE WHEN (ASCII(SUBSTRING(@INSTR COLLATE Latin1_General_BIN,NM.N,1)) - 48) BETWEEN 0 AND 9 THEN SUBSTRING(@INSTR,NM.N,1) END
FROM NUMS NM
FOR XML PATH('')
) AS OUT_STR
;
Test results
Beginning execution loop
========== Using nGrams ==========
1666
========== Using nGrams ==========
1680
========== Using nGrams ==========
1706
Batch execution completed 3 times.
Beginning execution loop
========== Using PatExclude8K ==========
2830
========== Using PatExclude8K ==========
2756
========== Using PatExclude8K ==========
2750
Batch execution completed 3 times.
Beginning execution loop
========== Jeff's Old Scalar Loop Function ==========
1596
========== Jeff's Old Scalar Loop Function ==========
1556
========== Jeff's Old Scalar Loop Function ==========
1556
Batch execution completed 3 times.
Beginning execution loop
========== dbo.STRIP_NUM_EE Function ==========
1393
========== dbo.STRIP_NUM_EE Function ==========
1343
========== dbo.STRIP_NUM_EE Function ==========
1343
Batch execution completed 3 times.

Brilliant work sir - very well done!

What is a little weird is how PatExclude8K is performing so bad on your system. It is doing much better than the nGrams solution on my system. Other than that I get similar results.

Beginning execution loop

========== Using nGrams ==========

2416

========== Using nGrams ==========

2420

========== Using nGrams ==========

2390

Batch execution completed 3 times.

Beginning execution loop

========== Using PatExclude8K ==========

2053

========== Using PatExclude8K ==========

2013

========== Using PatExclude8K ==========

2043

Batch execution completed 3 times.

Beginning execution loop

========== Jeff's Old Scalar Loop Function ==========

1840

========== Jeff's Old Scalar Loop Function ==========

1860

========== Jeff's Old Scalar Loop Function ==========

1840

Batch execution completed 3 times.

Beginning execution loop

========== dbo.STRIP_NUM_EE Function ==========

1690

========== dbo.STRIP_NUM_EE Function ==========

1690

========== dbo.STRIP_NUM_EE Function ==========

1673

Batch execution completed 3 times.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

Eirikur Eiriksson (10/28/2014)
Little clean up in the logic, now it looks pretty good
😎
/********************************************************************
-- Stripping out any non-numerical characters
-- EE 2014-10-28 Inital coding
-- EE Cleaned up comparison logic
********************************************************************/
ALTER FUNCTION dbo.STRIP_NUM_EE
(
@INSTR VARCHAR(8000)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
,NUMS(N) AS (SELECT TOP(CONVERT(BIGINT,LEN(@INSTR),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4)
SELECT
(
SELECT
CASE WHEN (ASCII(SUBSTRING(@INSTR COLLATE Latin1_General_BIN,NM.N,1)) - 48) BETWEEN 0 AND 9 THEN SUBSTRING(@INSTR,NM.N,1) END
FROM NUMS NM
FOR XML PATH('')
) AS OUT_STR
;
Test results
Beginning execution loop
========== Using nGrams ==========
1666
========== Using nGrams ==========
1680
========== Using nGrams ==========
1706
Batch execution completed 3 times.
Beginning execution loop
========== Using PatExclude8K ==========
2830
========== Using PatExclude8K ==========
2756
========== Using PatExclude8K ==========
2750
Batch execution completed 3 times.
Beginning execution loop
========== Jeff's Old Scalar Loop Function ==========
1596
========== Jeff's Old Scalar Loop Function ==========
1556
========== Jeff's Old Scalar Loop Function ==========
1556
Batch execution completed 3 times.
Beginning execution loop
========== dbo.STRIP_NUM_EE Function ==========
1393
========== dbo.STRIP_NUM_EE Function ==========
1343
========== dbo.STRIP_NUM_EE Function ==========
1343
Batch execution completed 3 times.

You've just gotta love pure math instead of character based calculations. Thanks and well done, Eirikur. Now I have to test similar logic in a While Loop and see if that's any faster.

It also shows that general purpose functions have general purpose performance. Functions with a specific purpose are usually faster. It's a lesson that I learned a long time ago and have apparently lost my mind because I sure didn't apply it here. So, thinks for the well deserved kick in the head to shake me out of it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

Jeff Moden (10/28/2014)
I took the two contenders from your good post, Alan, and added on of my own. Here's the code for the function I use for such things. Yeah... you'll be shocked. It's not only a scalar function but it also has a WHILE loop in it.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CleanString]
/*******************************************************************************
Purpose:
Given a string and a pattern of characters to remove, remove the patterned
characters from the string.
Usage:
--===== Basic Syntax Example
SELECT CleanedString = dbo.CleanString(@pSomeString,@pPattern)
;
--===== Remove all but Alpha characters
SELECT CleanedString = dbo.CleanString(st.SomeString,'%[^A-Za-z]%');
FROM dbo.SomeTable st
;
--===== Remove all but Numeric digits
SELECT CleanedString = dbo.CleanString(st.SomeString,'%[^0-9]%');
FROM dbo.SomeTable st
;
Programmer Notes:
1. @pPattern is case sensitive.
2. The pattern set of characters must be for just one character.
Revision History:
Rev 00 - Circa 2007 - George Mastros?
- Initial find on the web
Rev 01 - 29 Mar 2007 - Jeff Moden
- Optimize to remove one instance of PATINDEX from the loop.
- Add code to use the pattern as a parameter.
Rev 02 - 26 May 2013 - Jeff Moden
- Add case sensitivity
*******************************************************************************/
(@pString VARCHAR(8000),@pPattern VARCHAR(100))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @Pos SMALLINT;
SELECT @Pos = PATINDEX(@pPattern,@pString COLLATE Latin1_General_BIN);
WHILE @Pos > 0
SELECT @pString = STUFF(@pString,@Pos,1,''),
@Pos = PATINDEX(@pPattern,@pString COLLATE Latin1_General_BIN);
RETURN @pString;
END
;
Here's the test harness that I used. It runs each function through the 100K row table 3 times.
--===== Create the 100K row test table
IF OBJECT_ID('tempdb..#val') IS NOT NULL DROP TABLE #val
;
SELECT TOP 100000
txt = ISNULL(CONVERT(VARCHAR(36),NEWID()),'')
INTO #val
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
ALTER TABLE #Val
ADD PRIMARY KEY CLUSTERED (txt)
;
--===== Do the tests. Had to use duration because one
-- of the tests is on the new scalar function and
-- SET STATISTICS doesn't report on those correctly.
GO
PRINT '========== Using nGrams ==========';
DECLARE @String VARCHAR(36)
,@StartTime DATETIME
;
SELECT @StartTime = GETDATE()
;
SELECT @string = CleanedText
FROM #val
CROSS APPLY dbo.StripNonNumeric_itvf_ajb(txt)
;
PRINT DATEDIFF(ms,@StartTime,GETDATE())
;
GO 3
PRINT '========== Using PatExclude8K ==========';
DECLARE @String VARCHAR(36)
,@StartTime DATETIME
;
SELECT @StartTime = GETDATE()
;
SELECT @string = newstring
FROM #val
CROSS APPLY dbo.PatExclude8K(txt, '[^0-9]')
;
PRINT DATEDIFF(ms,@StartTime,GETDATE())
;
GO 3
PRINT '========== Jeff''s Old Scalar Loop Function ==========';
DECLARE @String VARCHAR(36)
,@StartTime DATETIME
;
SELECT @StartTime = GETDATE()
;
SELECT @String = dbo.CleanString(txt,'%[^0-9]%')
FROM #val
;
PRINT DATEDIFF(ms,@StartTime,GETDATE())
;
GO 3
Here are the run results. Again, you'll be shocked. This is the one place where I've not been able to make a Tally Table solution able to beat it. Lord knows I and other good folks have tried.
(100000 row(s) affected)
Beginning execution loop
========== Using nGrams ==========
2916
========== Using nGrams ==========
2893
========== Using nGrams ==========
2890
Batch execution completed 3 times.
Beginning execution loop
========== Using PatExclude8K ==========
2703
========== Using PatExclude8K ==========
2640
========== Using PatExclude8K ==========
2653
Batch execution completed 3 times.
Beginning execution loop
========== Jeff's Old Scalar Loop Function ==========
2413
========== Jeff's Old Scalar Loop Function ==========
2500
========== Jeff's Old Scalar Loop Function ==========
2466
Batch execution completed 3 times.

That is an excellent function Jeff, thanks for sharing that. It took a a few reads to understand how it works. Interesting to see a scalar function with a loop perform so well. I've tested Eirikur's solution on and it is getting the best performance. I started to make a version of Eirikur's that takes a pattern but I'm out of gas for the day. I am going to re-factor his code in the morning to take a pattern as a parameter and re-test the performance.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

So I made the following change to Eirikur's function to so that it takes a pattern as a parameter:

-- refactoring...

CREATE FUNCTION dbo.STRIP_NUM_EE_PAT

(

@INSTR VARCHAR(8000),

@PATTERN VARCHAR(50)

)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

,NUMS(N) AS (SELECT TOP(CONVERT(BIGINT,LEN(@INSTR),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4)

SELECT

(

SELECT

CASE WHEN PATINDEX(@PATTERN,SUBSTRING(@INSTR COLLATE Latin1_General_BIN,NM.N,1)) = 0 THEN SUBSTRING(@INSTR,NM.N,1) END

FROM NUMS NM

FOR XML PATH('')

) AS OUT_STR

;

GO

and ran the following test a few times:

--===== Create the 100K row test table

IF OBJECT_ID('tempdb..#val') IS NOT NULL DROP TABLE #val

;

SELECT TOP 100000

txt = ISNULL(CONVERT(VARCHAR(36),NEWID()),'')

INTO #val

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

ALTER TABLE #Val

ADD PRIMARY KEY CLUSTERED (txt)

;

--===== Do the tests. Had to use duration because one

-- of the tests is on the new scalar function and

-- SET STATISTICS doesn't report on those correctly.

GO

PRINT '========== Using PatExclude8K ==========';

DECLARE @String VARCHAR(36)

,@StartTime DATETIME

;

SELECT @StartTime = GETDATE()

;

SELECT @string = newstring

FROM #val

CROSS APPLY dbo.PatExclude8K(txt, '[^0-9]')

;

PRINT DATEDIFF(ms,@StartTime,GETDATE())

;

GO 3

PRINT '========== Jeff''s Old Scalar Loop Function ==========';

DECLARE @String VARCHAR(36)

,@StartTime DATETIME

;

SELECT @StartTime = GETDATE()

;

SELECT @String = dbo.CleanString(txt,'%[^0-9]%')

FROM #val

;

PRINT DATEDIFF(ms,@StartTime,GETDATE())

;

GO 3

PRINT '========== EE ==========';

DECLARE @String VARCHAR(36)

,@StartTime DATETIME

;

SELECT @StartTime = GETDATE()

;

SELECT @string = OUT_STR

FROM #val

CROSS APPLY dbo.STRIP_NUM_EE(txt);

;

PRINT DATEDIFF(ms,@StartTime,GETDATE());

GO 3

PRINT '========== EE_PAT ==========';

DECLARE @String VARCHAR(36)

,@StartTime DATETIME

;

SELECT @StartTime = GETDATE()

;

SELECT @string = OUT_STR

FROM #val

CROSS APPLY dbo.STRIP_NUM_EE_PAT(txt,'[^0-9]');

;

PRINT DATEDIFF(ms,@StartTime,GETDATE());

GO 3

Though the new version appears to produce the exact some query plan it the version that uses PATINDEX seems to perform a little faster....

Beginning execution loop

========== Using PatExclude8K ==========

1993

========== Using PatExclude8K ==========

2073

========== Using PatExclude8K ==========

1993

Batch execution completed 3 times.

Beginning execution loop

========== Jeff's Old Scalar Loop Function ==========

1803

========== Jeff's Old Scalar Loop Function ==========

1840

========== Jeff's Old Scalar Loop Function ==========

1810

Batch execution completed 3 times.

Beginning execution loop

========== EE ==========

1766

========== EE ==========

1716

========== EE ==========

1703

Batch execution completed 3 times.

Beginning execution loop

========== EE_PAT ==========

1626

========== EE_PAT ==========

1576

========== EE_PAT ==========

1563

Batch execution completed 3 times.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 6

First, hats off to Eirikur Eiriksson for the reminders about the performance of dedicated rather than general purpose functions and the speed associated with integer comparisons rather than character-based comparisons. With that, I made a couple of tweaks to Eirikur's good code for another bit of performance. Here's the function I came up with as a result. As usual, details are in the code. If someone needs this for 2005, just change the VALUES function to 10 individual SELECT NULL UNION ALL statements (the last one not having UNION ALL) and it'll work in 2005.

CREATE FUNCTION dbo.DigitsOnly

/******************************************************************************************************************************

Purpose:

Given a VARCHAR(8000) or less string, return only the numeric digits from the string.

Programmer's Notes:

1. This is an iTVF (Inline Table Valued Function) that will be used as an iSF (Inline Scalar Function) in that it returns a

single value in the returned table and should normally be used in the FROM clause as with any other iTVF.

2. CHECKSUM returns an INT and will return the exact number given if given an INT to begin with. It's also faster than a CAST

or CONVERT and is used as a performance enhancer by changing the BIGINT of ROW_NUMBER() to a more appropriately sized INT.

3. Another performance enhancement is using a WHERE clause calculation to prevent the relatively expensive XML PATH

concatentation of empty strings normally determined by a CASE statement in the XML "loop".

4. Another performance enhancement is not making this function a generic function that could handle a pattern. That allows

us to use all integer math to do the comparison using the high speed ASCII function convert characters to their numeric

equivalent. ASCII characters 48 through 57 are the digit characters of 0 through 9.

Kudos:

1. Hats off to Eirikur Eiriksson for the ASCII conversion idea and for the reminders that dedicated functions will always

be faster than generic functions and that integer math beats the tar out of character comparisons that use LIKE or

PATINDEX.

2. Hats off to all of the good people that submitted and tested their code on the following thread. It's this type of

participation and interest that makes code better. You've just gotta love this commmunity.

http://www.sqlservercentral.com/Forums/Topic1585850-391-2.aspx#bm1629360

Usage:

--===== CROSS APPLY example

SELECT ca.DigitsOnly

FROM dbo.SomeTable

CROSS APPLY dbo.DigitsOnly(SomeVarcharCol) ca

;

Revision History:

Rev 00 - 29 Oct 2014 - Jeff Moden - Initial Creation

-

******************************************************************************************************************************/

--===== Declare the I/O for this function

(@pString VARCHAR(8000))

RETURNS TABLE WITH SCHEMABINDING AS RETURN

WITH E1(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

,Tally(N) AS (SELECT TOP (LEN(@pString)) CHECKSUM(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) FROM E1 a,E1 b,E1 c,E1 d)

SELECT DigitsOnly =

(

SELECT SUBSTRING(@pString,N,1)

FROM Tally

WHERE ASCII(SUBSTRING(@pString,N,1)) BETWEEN 48 AND 57

FOR XML PATH('')

)

;

Here's the test-harness code that I used for the performance tests. It uses the same table we've been using.

PRINT '========== Erikur''s Function ==========';

GO

DECLARE @String VARCHAR(36)

,@StartTime DATETIME

;

SELECT @StartTime = GETDATE()

;

SELECT @String = OUT_STR

FROM #val

CROSS APPLY dbo.STRIP_NUM_EE(txt)

;

PRINT DATEDIFF(ms,@StartTime,GETDATE())

;

GO 3

PRINT '========== Jeff''s Modification of Erikur''s Function ==========';

GO

DECLARE @String VARCHAR(36)

,@StartTime DATETIME

;

SELECT @StartTime = GETDATE()

;

SELECT @String = ca.DigitsOnly

FROM #val

CROSS APPLY dbo.DigitsOnly(txt) ca

;

PRINT DATEDIFF(ms,@StartTime,GETDATE())

;

GO 3

Here are the run results from the same test harness that we've been using...

========== Erikur's Function ==========

Beginning execution loop

2626

2650

2563

Batch execution completed 3 times.

========== Jeff's Modification of Erikur's Function ==========

Beginning execution loop

2083

2073

2133

Batch execution completed 3 times.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 7

Alan.B (10/29/2014)
So I made the following change to Eirikur's function to so that it takes a pattern as a parameter:

Interesting... (our posts crossed paths). I'll take a look at that tonight, Alan. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 8

Thanks Alan and Jeff, very good job indeed!

This is really the SSC community at it's best and although I haven't had any time to look properly into this, Alan and Jeff have picked it up and pushed it further, Chapeau!

Looking at Jeff's brilliant improvements (nice trick with the checksum!), I can see an opportunity for further improvement, mainly the logical operator BETWEEN. It has roughly 1/4 of the total cost. I replaced it with a single comparison operator using a little bit-bashing and got the filter cost down to about 1/5.

😎

CREATE FUNCTION dbo.DigitsOnlyEE

/* Jeff's comment from his function goes here */

(@pString VARCHAR(8000))

RETURNS TABLE WITH SCHEMABINDING AS RETURN

WITH E1(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

,Tally(N) AS (SELECT TOP (LEN(@pString)) (CHECKSUM(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))) FROM E1 a,E1 b,E1 c,E1 d)

SELECT DigitsOnly =

(

SELECT SUBSTRING(@pString,N,1)

FROM Tally

WHERE ((ASCII(SUBSTRING(@pString,N,1)) - 48) & 0x7FFF) < 10

FOR XML PATH('')

)

;

GO

Test result using Jeff's test harness

Beginning execution loop

========== dbo.DigitsOnly Function ==========

1306

========== dbo.DigitsOnly Function ==========

1286

========== dbo.DigitsOnly Function ==========

1310

Batch execution completed 3 times.

Beginning execution loop

========== dbo.DigitsOnlyEE Function ==========

1113

========== dbo.DigitsOnlyEE Function ==========

1123

========== dbo.DigitsOnlyEE Function ==========

1153

Batch execution completed 3 times.

Edit: missed the second byte 0x7FFF.

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 9

Tweaking the code a little bit more shaves off approximately 17 percent, here is an all in one code

😎

USE tempdb;

GO

SET NOCOUNT ON;

--===== Create the 100K row test table

IF OBJECT_ID('tempdb..#val') IS NOT NULL DROP TABLE #val

;

SELECT TOP 1000000

txt = ISNULL(CONVERT(VARCHAR(36),NEWID()),'')

INTO #val

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

ALTER TABLE #Val

ADD PRIMARY KEY CLUSTERED (txt)

;

GO

/* Jeff's Modification of Erikur's Function */

IF OBJECT_ID('dbo.DigitsOnly') IS NOT NULL DROP FUNCTION dbo.DigitsOnly;