Good point Luis! It is also around 30% faster,

Quick comparison

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @INT_BUCKET INT = 0;

DECLARE @CHAR_BUCKET CHAR(7) = '';

DECLARE @TIMING_RESULTS TABLE

(

TR_ID INT IDENTITY(1,1) NOT NULL

,TR_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME())

,TR_TX VARCHAR(100) NOT NULL

);

DECLARE @SAMPLE_SIZE INT = 1000000;

DECLARE @PADDING CHAR(7) = '0000000';

INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('RIGHT( @SAMPLE_SIZE + NM.N, 7)');

;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(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

SELECT

@INT_BUCKET = NM.N -- AS INT_NUM

,@CHAR_BUCKET = RIGHT( @SAMPLE_SIZE + NM.N, 7) -- AS MATH_PADDED_NUM

FROM NUMS NM;

INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('RIGHT( @SAMPLE_SIZE + NM.N, 7)');

INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('STUFF(@PADDING,LEN(@PADDING)-(LEN(NM.N)-1),LEN(NM.N),NM.N)');

;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(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)

SELECT

@INT_BUCKET = NM.N --AS INT_NUM

,@CHAR_BUCKET = STUFF(@PADDING,LEN(@PADDING)-(LEN(NM.N)-1),LEN(NM.N),NM.N) --AS PADDED_NUM

FROM NUMS NM;

INSERT INTO @TIMING_RESULTS(TR_TX) VALUES('STUFF(@PADDING,LEN(@PADDING)-(LEN(NM.N)-1),LEN(NM.N),NM.N)');

SELECT

TR.TR_TX AS OPERATION

,DATEDIFF(MICROSECOND, MIN(TR.TR_TS), MAX(TR.TR_TS)) AS DURATION

FROM @TIMING_RESULTS TR

GROUP BY TR.TR_TX

ORDER BY 2;

Results

OPERATION DURATION

------------------------------------------------------------- -----------

RIGHT( @SAMPLE_SIZE + NM.N, 7) 594801

STUFF(@PADDING,LEN(@PADDING)-(LEN(NM.N)-1),LEN(NM.N),NM.N) 873601