June 25, 2021 at 3:13 am
When is it kosher to use calculated columns in SQL Server? On complex calculations that only need to be made once? or do you do it only after you determine that the calculation takes too long? is it better to create a materialized view? (Amazing all the stuff I never learned doing the certification exam, isn't it?)
June 25, 2021 at 3:57 am
Brent Ozar wrote an article about how they can force you query to go single threaded even if you're query is absolutely perfect in every other way and even when the query makes absolutely no reference to the computed column. He advocates the use of triggers instead of calculated columns and based on the testing I did with his code but on a much larger scale, I have to agree with him.
https://www.brentozar.com/archive/2020/10/using-triggers-to-replace-scalar-udfs-on-computed-columns/
Considering the pain in the butt they can be in a whole lot of cases, there are other places where you might want to consider the use of triggers to maintain such a column instead of using even persisted computed columns that don't contain a UDF.
As for when to use such a computed column no matter how you decide to keep it up to date, there are a lot of scenarios and so it brings the dreaded but too true "It Depends" statement in.
On example is a table that has a start and end date. The "designers" of the table decided that they'd use NULL if there was no end date. Of course, someone wants to find out which rows were applicable for a month ago. That means that you WHERE clause has to look like this...
WHERE @SomeDate >= StartDate AND (@SomeDate <= EndDate OR EndDate IS NULL)
Guess what that does to SARGability? In the dumper that goes.
Since the Dev team would raise hell and prop it up on a stick if you gave the column a default of say '99990101' (just '9999' would do if it's really a temporal datatype, and it should be) because they'd need to retest all their reports and a good part of the front end and all of their storeprocedures, etc, etc, you could at least stop the bleeding on any new stuff by adding a RealEndDate column where it contained the actual date from the EndDate column or the '9999' date if the EndDate was NULL.
Basically, if something will have a value until another column changes, it's a possible candidate for a computed column. You can also do things to make things easier for reporting. For example, if you have a bunch of developers that regularly need to do monthly reporting but can't for the life of them learn how to select a month of data without all sorts of non-SARGable code, you could build a special column just for them.
If you have a full name column and (for example) the last name always comes first and is always followed by a comma and you need to do a whole lot of lookups by first name, instead of build non-SARGable middle-of-the-string lookups, you could create a computed column to only contain what comes after the comma and, bingo! You suddenly have SARGable queries. 😀
Indexed views can be a godsend but remember... they constantly updated and they do have indexes. That can create one hell of a load on the system when the underlying index get updated and especially if they end up doing page splits. I'd be real careful with those. With as often as we insert and update data, I try to avoid them.
And last but not least, thank you for your unabashed comment on the certs. I could go on a tear about them but I'll just leave it at that. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2021 at 5:35 pm
And last but not least, thank you for your unabashed comment on the certs. I could go on a tear about them but I'll just leave it at that. 😀
C'mon - would love to see that rant 🙂
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply