FORMAT is a convenient but expensive SQL Server function - Part 1

By: Aaron Bertrand   |   Updated: 2022-02-16   |   Comments (2)   |   Related: 1 | 2 | 3 | 4 | 5 | > Functions System

Problem

I once demonstrated how FORMAT is about twice as expensive as CONVERT in a Microsoft SQL Server database. That comparison may have been a bit unfair - what about more convoluted CONVERT expressions? Are there other ways to have the convenience of FORMAT date function without the overhead at query time in a SQL database?

Solution

First, in the 2015 blog post, I compared these two T-SQL date format approaches:

SELECT FORMAT(sysdatetime(), 'yyyy-MM-dd'),
       CONVERT(char(10), sysdatetime(), 120);

Admittedly, the SQL CONVERT function is trivial, and many of us generate more specialized output than simple ISO-type strings. Let's consider an even simpler SQL FORMAT expression that produces a more human readable output (though, to be completely transparent, this is really the job of the presentation layer):

DECLARE @d date = '20220114';
SELECT FORMAT(@d, 'D', 'en-us');

This syntax produces the following output:

Friday, January 14, 2022

To get the same output without FORMAT, we'd need a SELECT statement like the following:

SET LANGUAGE us_english;
DECLARE @d date = '20220114';
SELECT CONCAT_WS(', ', 
                 DATENAME(WEEKDAY, @d), 
                 DATENAME(MONTH, @d) + ' ' 
                   + CONVERT(varchar(2), DATEPART(DAY, @d)), 
                 DATEPART(YEAR, @d)
       );

This latter expression is clearly much more tedious to write, and not all that pretty to look at, either. And we need to start with the SET LANGUAGE statement to match the same locale specifier that FORMAT affords inline, because a user's own language settings may be different from the one we use during testing. (For completeness, though, I will mention that you usually want the user's own language settings to dictate output format.)

I have had conversations with many peers who need more complex expressions like this, and they really appreciate the simplification FORMAT offers, as well as the consistency with other languages like C#. They often suggest that, surely, all these extra computations will pull the performance down closer to FORMAT. Well, we can test this! Let's create a database with Query Store enabled:

USE master;
GO

DROP DATABASE IF EXISTS FormatTest;
GO

CREATE DATABASE FormatTest;
GO

ALTER DATABASE FormatTest SET QUERY_STORE = ON (QUERY_CAPTURE_MODE = ALL);
GO

USE FormatTest;
GO

Next, we can create a table with several thousand rows:

SELECT o.*, column_name = c.name 
  INTO dbo.Columns
  FROM sys.all_objects AS o 
  INNER JOIN sys.all_columns AS c 
  ON o.[object_id] = c.[object_id];

CREATE CLUSTERED INDEX cix ON dbo.Columns(name, column_name);

And run simple queries that scan the entire table and transform the modify_date value in each row:

SET NOCOUNT ON;
GO

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO

DECLARE @d varchar(50);

SELECT  /* convert1 */ @d = CONCAT_WS(', ', 
    DATENAME(WEEKDAY, modify_date), 
    DATENAME(MONTH, modify_date) + ' ' 
      + CONVERT(varchar(2), DATEPART(DAY, modify_date)), 
    CONVERT(char(4), DATEPART(YEAR, modify_date)))
  FROM dbo.Columns;
GO 50

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO

DECLARE @d varchar(50);

SELECT  /* format */ @d = FORMAT(modify_date, 'D', 'en-us') 
  FROM dbo.Columns;
GO 50

If we look at the metrics from Query Store, we can see that my earlier observations about FORMAT and its CLR overhead are still true even up against this more complex expression:

One reason people tend to gravitate toward FORMAT (even in spite of its performance overhead) is that they don't want to muck up their queries with complex expressions like the one I've listed above. Another way to avoid that is to hide the complex functionality away in a user-defined function. Let's try scalar and inline table-valued functions to hide the expression away:

CREATE FUNCTION dbo.PrettyDate_Scalar
(
    @date date
)
RETURNS varchar(50)
WITH SCHEMABINDING
AS
BEGIN
  RETURN (SELECT /* scalar */ PrettyDate = CONCAT_WS(', ', 
    DATENAME(WEEKDAY, @date), 
    DATENAME(MONTH, @date) + ' ' 
      + CONVERT(varchar(2), DATEPART(DAY, @date)), 
    CONVERT(char(4), DATEPART(YEAR, @date)))
  );
END
GO

CREATE FUNCTION dbo.PrettyDate_InlineTVF
(
    @date date
)
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN (SELECT /* inlineTVF */ PrettyDate = CONCAT_WS(', ', 
    DATENAME(WEEKDAY, @date), 
    DATENAME(MONTH, @date) + ' ' 
      + CONVERT(varchar(2), DATEPART(DAY, @date)), 
    CONVERT(char(4), DATEPART(YEAR, @date)))
  );
GO

Then we can add these queries to our test rig:

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO

DECLARE /* scalar */ @d varchar(50);

SELECT  /* scalar */ @d = dbo.PrettyDate_Scalar(modify_date) 
  FROM dbo.Columns;
GO 50

DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
GO

DECLARE /* inlineTVF */ @d varchar(50);

SELECT  /* inlineTVF */ @d = f.PrettyDate
  FROM dbo.Columns AS c
  CROSS APPLY dbo.PrettyDate_InlineTVF(c.modify_date) AS f;
GO 50

Results:

These are just microseconds, and so these differences might not be obvious in local testing, but this is a different universe and will be much more noticeable at scale.

Note when using inline formatting or functions, we could easily add additional formatting that FORMAT doesn't offer; for example, we could add suffixes on the day name, like 1st, 2nd 3rd, 4th, etc.

     + CONVERT(varchar(2), DATEPART(DAY, @d))
    + CASE WHEN DATEPART(DAY, @d) BETWEEN 10 AND 19 THEN 'th'
           WHEN DATEPART(DAY, @d) % 10 = 1 THEN 'st'
           WHEN DATEPART(DAY, @d) % 10 = 2 THEN 'nd'
           WHEN DATEPART(DAY, @d) % 10 = 3 THEN 'rd'
           ELSE 'th' END,

Adding this additional formatting logic to the inline convert query and a new table-valued function produces these results:

While there is a tangible bump, we're still under the 10 millisecond range, remaining way better than FORMAT and its runtime approaching 200 milliseconds.

There are other ways to simplify queries and still get this output (other than letting the application layer handle it). Next time, we'll look at ways we can prevent the query from having to perform any formatting at all, namely with a computed column or a DML trigger.

Conclusion

While I like the idea of FORMAT because it makes for more convenient expressions that are consistent with other languages, the performance overhead is just too significant to ignore. When I suggest against its use, I often hear responses like, "Well, I'm only using it in this one place" or "This table will never get large." My issue with this logic is that it's very hard to draw the line, and it's very easy for your peers to "learn" that the practice is okay, and in turn use it in other scenarios where it won't be okay.

One other downside to FORMAT – if, like me, you are playing with using Azure SQL Edge for local development, this is one of the features not supported there (see this previous tip for some other differences).

Next Steps

See these tips and other resources:

• FORMAT() is nice and all, but…
• Format SQL Server Dates with FORMAT Function
• SQL Server Date and Time Functions with Examples
• Determine SQL Server Date and Time Parts with DATEPART and DATENAME Functions
• SQL Convert Date to YYYY-MM-DD hh
• SQL Server Date Functions Tutorial
• Add and Subtract Dates using DATEADD in SQL Server
• How to Get Current Date with GETDATE() in SQL Server

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips