Best Practices for Computed Column in SQL Server
I'm working with a user table and want to put a "end of probational period date". Basically, each new user has 2 full months from when they join as part of their probation period. I saw that I can put a formula in the column for my user table, but I'm wondering if I should have a script that updates this instead or if this is an acceptable time to use computed columns. I access this table for various things, and will occasionally update the users' row based on performance milestone achievements. The Application Date will never change/be updated.
My question is: Is using the computed column a good practice in this situation or will it recompute each time I update that row (even though I'm not going to update the App Date)? I don't want to create more overhead when I update the row in the future.
Formula I'm using in the column definition for the Probation End Date:
(dateadd(day,(-1),dateadd(month,(3),dateadd(day,(1)-datepart(day,[APP_DT]),[APP_DT]))))
Answer
Seeing that this date most likely will never change once it's set, it's probably not a good candidate for a computed column.
After all: once you insert a row into that table, you can easily calculate that "end of probation period" date right there and then (e.g. in a trigger), and once set, that date won't ever change.
So while you can definitely do it this way, I would probably prefer to use a AFTER INSERT trigger (or a stored procedure for the INSERT operation) that just calculates it once, and then stores that date.
Also, just as a heads-up: a computed column with just the formula is being calculated every time to access it - just be aware of that. That is, unless you specify the PERSISTED keyword, in that case, the result is stored along side the other data in the row, and this would be a much better fit here - again, since that value, once calculated, is not bound to change ever again.