BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andy Brown on 16 January 2017
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Assessing the speed of the FORMAT function in T-SQL
The FORMAT function was a welcome addition in SQL Server 2012, since it allows you to format dates so much more easily. For example, this SQL:
-- use any custom date format
SELECT FORMAT(
GetDate(),
'dddd dd MMMM yyyy'
) AS 'Beautiful date'
Would give this output (today - worryingly - is Friday 13th January):
Here we've chosen to display the day name, number, month name and year.
SQL Server 2016 adds an optional third argument to the FORMAT function, allowing you to return language-specific formatting.
However, this feature comes with a price: it's slow, everyone says. But how slow? This blog attempts to answer the question.
Creating a test table
To test this out, I've generated a table with 5,000,000 rows (although only 3 columns). First I manually created a table:
The table includes 3 columns: a unique row number (which is an identity column), the row name and a somewhat random date.
I then ran the following query to generate test data:
-- get rid of any old data
TRUNCATE TABLE tblRow
-- the number of rows to generate
DECLARE @max int = 5000000
-- various variables used
DECLARE @i int = 1
DECLARE @d int
DECLARE @m int
DECLARE @s varchar(100)
-- keep going till we've created
-- enough rows
WHILE @i <= @max
BEGIN
-- use modulus to generate different days
-- and months (not random, but this
-- won't matter)
SET @d = @i % 5 + 1
SET @m = @i % 7 + 2
-- generate a date in 2017 with this day
-- and month
SET @s = '2017-0' +
CAST(@m AS varchar(1)) + '-0' +
CAST(@d as VARCHAR(1))
-- add this date into our table
INSERT INTO tblRow (RowName,RowDate)
VALUES ('test ' + CAST(@i as varchar(50)),
cast(@s as date))
SET @i = @i + 1
END
SELECT * FROM tblRow
The result is an uninspiring table of dates, the first few of which are as follows:
Bedtime reading this isn't, but we have 5 million rows containing a date field.
The test queries
What I'm interested to know is how much time it takes SQL Server to show dates, using 3 different methods:
- without formatting;
- using the CONVERT function (the traditional way); and
- using the FORMAT function
To people who say that you shouldn't use SQL for formatting, I say: read a different blog!
The SQL used is as follows, with each of the 3 lines of code commented in:
SELECT
r.RowName,
-- show one (and one only) of the following
-- 1) r.RowDate
-- 2) convert(char(10),r.RowDate,103)
-- 3) format(r.RowDate,'dd/MM/yyyy')
FROM
tblRow AS r
The results compared
So here is how long each query took to run, on a Dell Precision M6800 laptop with this specification, and nothing much else running in the background apart from SQL Server Management Studio:
The laptop I'm using!
The results?
| Method | Description | Seconds |
|---|---|---|
| 1 | Date only, without formatting | 18 |
| 2 | Using CONVERT function | 21 |
| 3 | Using FORMAT function | 46 |
So the conclusion is that the FORMAT function - at least for this example - takes over twice as long.
Does this matter? That depends on how many rows of data you are querying, and how long you mind waiting for the results. Your call!