I am an accidental DBA charged with performance tuning a mess of queries (both literal, and figurative).

One pattern we have is to always store Dates as DateTime2(7) regardless whether we need the time portion or not.

When querying these, one "smart" developer created a scalar function DateOnly() and used that everywhere (figuratively). As you know, and I found out recently, scalar functions usually cause a query to stay single threaded, so I've been working to inline the function as a simple cast(dateColumn as Date).

For performance reasons, I suggested converting the columns to just Date for the tables where Time is not important and was met with resistance.

I recently learned (I did mention that I'm an accidental DBA) about computed columns and wonder if maybe that's a good solution? I'm just not sure about any performance relate issues here and was wondering if there is any sort of best practice.

Yes, I googled it, no I didn't find anything. That's why I'm here.

How would you approach the problem, and why?

Inline conversion
Computed Column
Persisted Computed Column
Computed Column with Index

  • Is persisted the same as materialized? – Walter Mitty Feb 22 at 23:50
  • You may want to edit your question and add a tag for the type of database you are using (SQL Server, MySQL, MariaDB, etc). – Dave Feb 23 at 12:30
  • Also worth mentioning, SQL Server 2019 should be able to inline this function automatically if you use compability level 150. – Piotr Feb 24 at 2:24
  • In the cases where you don't care about the time portion do you always store the time as 00:00? So when used in WHERE or GROUP BY there is no need to CAST anyway? Just when returning in a SELECT list – Martin Smith Feb 24 at 8:10
  • @piotr True, except when used in group by functions. At least that's my understanding. – Kevon Feb 24 at 22:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.