An interesting customer question explained

While at a customer today I was given an interesting question. The individual was writing reports and needed to have right-justified, zero padded fields. He was investigating the FORMAT function which was introduced in SQL Server 2012. He had heard some mumblings that the FORMAT function was not as performant as good ole number stuffing. He wanted to understand why.

So basically, he wanted a cook-off between

SELECT FORMAT(N, ‘d10’) as padWithZeroes FROM FormatTest;

and

SELECT RIGHT(‘0000000000’+RTRIM(CAST (N as varchar(5))),10) from FormatTest;

I had a hunch but wanted empirical evidence…

so fire up ssms and our trusty query store and create a repro.

use AdventureWorks2016;
go

ALTER DATABASE CURRENT SET QUERY_STORE clear;
go

ALTER DATABASE CURRENT SET QUERY_STORE=OFF;
go

drop table if exists FormatTest;
go

create table FormatTest (N int not null)
go

insert into FormatTest (N) Values (CAST (RAND()*1000 AS int));
go 10000

ALTER DATABASE CURRENT SET QUERY_STORE (OPERATION_MODE = READ_WRITE, INTERVAL_LENGTH_MINUTES = 5)

SELECT FORMAT(N, 'd10') as padWithZeroes FROM FormatTest;
GO 100

SELECT RIGHT('0000000000'+RTRIM(CAST (N as varchar(5))),10) from FormatTest;
GO 100

;WITH querystoretext(query_text_id, query_sql_text)
AS (SELECT query_text_id,
query_sql_text
FROM
sys.query_store_query_text
WHERE query_sql_text = 'SELECT FORMAT(N, ''d10'') as padWithZeroes FROM FormatTest'
OR query_sql_text = 'SELECT RIGHT(''0000000000''+RTRIM(CAST (N as varchar(5))),10) from FormatTest')
,
planinfo(plan_id, query_text_id)
AS (SELECT plan_id,
query_text_id
FROM
sys.query_store_plan qsp
JOIN querystoretext
ON qsp.query_id = query_text_id)

SELECT count_executions,
avg_cpu_time,
--min_cpu_time,
--max_cpu_time,
--stdev_cpu_time,
avg_clr_time,
--min_clr_time,
--max_clr_time,
--stdev_clr_time,
pii.plan_id,
qst.query_sql_text
FROM
sys.query_store_runtime_stats AS qrs
JOIN planinfo AS pii
ON pii.plan_id = qrs.plan_id
JOIN querystoretext qst
ON qst.query_text_id = pii.query_text_id

And as I suspected FORMAT is utilizing the SQL CLR services as we see almost all the time being spent in CLR. Question answered.