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;
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.