Share via

An interesting customer question explained

  • Article

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.

[snippet slug=format-vs-stuff lang=sql]

So now to examine the Query Store runtime execution statistics

[snippet slug=query-store-query lang=sql]

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