SQL Server 2012 introduces many new string functions including CLR based functions. FORMAT is one of them which can be used for formatting dates, times and numeric. We have been using CONVERT function for formatting dates (refer this link for understanding different formatting: http://www.sqlusa.com/bestpractices/datetimeconversion/). However, with the introduction of new functions in SQL Server 2012, I see many recommendations on FORMAT function instead CONVERT. Question is, does it really help us on formatting?
FORMAT accepts date, time, and numeric values and returns a nvarchar value formatted based on format pattern specified with an optional cultural. In terms of usage, this is fairly easier than CONVERT, flexible, and many formatting options (See the syntax: http://technet.microsoft.com/en-us/library/hh213505.aspx). But what if it gives unexpected and poor performance?
Have a look on below code and the result. it is our traditional way for formatting the output. Note the time for completing the formatting on all records.
SELECT
SalesKey
, CONVERT(varchar(20), DateKey, 104) AS Date
, SalesQuantity
FROM dbo.FactSales
Here is the newest way.
SELECT
SalesKey
, FORMAT(DateKey, 'd', 'de-de') AS Date
, SalesQuantity
FROM dbo.FactSales
There is a clear indication that FORMAT does not give the same performance which CONVERT offers but the flexibility and additional options. Understanding the advantages and the fact that it relies on .NET framework CLR will definitely help you on writing efficient codes.
