formating date colums causing performance issues

Question

I have a query(written by my previous co-worker) with date columns returned as output but formatted, which s causing performance degradation (for below query returning 10k records, performance degraded from 1 to 2 seconds).

Should it be stored as formatted in the table itself(may not be possible to format all existing data though) or use some date/time table instead to join and get formatted date/time as a return column ?

Is there any other ways to increase the performance degradation caused by the formatted date return columns ?

Below query shows sample formatted date columns in query

SELECT   A.TransactionId ,
                             A.TransactionItemId ,
                             tid.TransactionItemDateId ,
                             tid.WorkDate ,
                             DATENAME (WEEKDAY, tid.WorkDate) AS DAY ,
                             CONCAT (DATENAME (WEEKDAY, tid.WorkDate), ' (', tid.WorkDate, ')') AS Code ,
                             tid.PayUnit ,
                             tid.BillUnit ,
                             FORMAT (CAST (tid.ClockIn AS DATETIME), 'HH:mm') ClockIn ,
                             FORMAT (CAST (tid.BreakIn AS DATETIME), 'HH:mm') BreakIn ,
                             FORMAT (CAST (tid.BreakOut AS DATETIME), 'HH:mm') BreakOut ,
                             FORMAT (CAST (tid.ClockOut AS DATETIME), 'HH:mm') ClockOut ,
                             ISNULL (tid.[Break], 0) [Break] ,
                             tidl.ListItem AS STATUS ,
                             tid.IsOverNight ,
                             osp.TransactionTemplateListItemId ,
                             tid.Note ,
                             tid.InvoiceText
                    FROM     #AllTransactionItem AS A
                             INNER JOIN #Txn t ON t.TransactionId = A.TransactionId
                             INNER JOIN dbo.TransactionItemDate AS tid ON tid.TransactionItemId = A.TransactionItemId
                             INNER JOIN dbo.TransactionLink AS tl ON tl.TransactionId = A.TransactionId
                             LEFT JOIN dbo.OrganizationServiceProfile AS osp ON osp.OrganizationId = tl.OrganizationId
                             LEFT JOIN dbo.ListItem tidl ON tidl.ListItemId = tid.EntryStatusListItemId
                             LEFT JOIN dbo.Shift AS s ON s.ShiftId = tl.ShiftId
                    ORDER BY tid.WorkDate;

Answer 1

Formatting should be a Presentation issue, not a Database one.

An Application or Reporting utility is far "closer" to the End User and what they consider a "date" to look like than the Database is.

Values should be stored in the correct Data Type for your database, formatted on the way out "to" the User and "un-formatted" on the way in "from" the User. (Users cannot enter Dates or Numbers - only Character Representations of them, which must be correctly interpreted).

Should it be stored as formatted in the table itself ...

Absolutely not.
As far as the likes of you or I are concerned:

Date values in database fields/tables have no format.

Only IEEE-aware Engineers who build DBMSs need to worry about internals like that.

Dates (and Numbers) should be considered "pure" data values that are stored they way your database wants them to be stored, purely for its own efficiency.
How the Users chooses to represent those values on their screens should be their concern.

Answer 2

formating date colums causing performance issues

Like Peter mentioned in the comments, FORMAT() is known to not be the most performant scalar function as Aaron Bertrand discusses in FORMAT() Is Nice And All, But….

Should it be stored as formatted in the table itself

Agreed with Phil, that you should store the original date field in it's native appropriate data type in the database. Aside from Phil's point on efficiency, it's also important from a data integrity standpoint. It's more direct to validate that a date is a date when it's stored as a date as opposed to as a formatted string, and therefore less likely you'll make a mistake when validating the integrity.

But I do think there are times when there's an exception to the rules too. As a developer, when thinking about the full stack and where the data will be consumed, sometimes certain business logic makes sense to store in the database layer to reduce redundancy and follow the D.R.Y. Principle (Don't Repeat Yourself). Following some software design principles do result in tradeoffs at the expense of other principles, and that's fine sometimes.

Now, I'm not saying you should store the original date data only formatted. I think you should still store it raw, in the proper data type, unformatted, so that you can utilize the efficiencies and data integrity benefits of doing so. But there are also tools you can use in SQL Server to persist the formatted version of it on disk, in addition to the original raw column:

Persisted Computed Column:

A computed column expression can use data from other columns to calculate a value for the column to which it belongs. You can specify an expression for a computed column in SQL Server

This allows you to write an expression and persist the results of that expression on disk, in the table itself, as another column of the table.

Indexed View

Creating a unique clustered index on a view improves query performance, because the view is stored in the database in the same way a table with a clustered index is stored.

This allows you to create a view that has its data persisted on disk, via the indexes on it, as if it was a table itself. Here you can also specify an expression as a new column that will be persisted.

By using either tool, you end up keeping the original raw data stored in its original data type column, so you don't lose any efficiencies or data integrity of that column. And any data changes in that original column will automatically recalculate the expression in the new column you create with either feature. So you don't have to do any management with running the expression manually later on, or waste time waiting on the expression to calculate on the fly every time a query selecting that field executes, like your current query does.

You basically get the best of both worlds with this kind of implementation.