Best Practices for Computed Column in SQL Server

Question

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]))))

Are "join date" and "application date" the same thing? Do you want to later be able to update the probation end date independently of the other date column(s)? — Aaron Bertrand, Apr 05 '13 at 14:45
Will the 2 month policy ever change? Could it become 1 or 3 (or some other value) in the future? — Damien_The_Unbeliever, Apr 05 '13 at 14:49
Yes, JoinDate/AppDate same thing (sorry, I use them interchangeably). I won't ever override the date, but to marc_s point, since it's not going to be updated, I'll include that as part of the insert script. I need to index this too, and without the persisted, that's not going to work so well. Thanks a million for the lightning quick reply! — Brian, Apr 05 '13 at 14:52
Yes, the policy could potentially change, but if it did it would apply to all existing users. Very good point too. — Brian, Apr 05 '13 at 14:54

score 15 · Accepted Answer · answered Apr 05 '13 at 14:46

15

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.

answered Apr 05 '13 at 14:46

marc_s

732,580
175
1,330
1,459

Thank you for the reply, so a better use of a calculated column might be where there is constantly shifting data. I have a milestone achievement tally, where various achievements met increase a persons 'score'. That sounds like a more appropriate use of the calculated column, correct? – Brian Apr 05 '13 at 14:54
@Brian: yes, something like that. Or if you have a date that might change over time, and for some queries you need to select or group by that date's month and year - then a computed (and persisted!) month and year for that date would be perfect, since you can also index those persisted, computed columns quite easily. – marc_s Apr 05 '13 at 15:12
I have run into the situation where I needed to use a formula larger than the 128 char limit on default value formula. Aaron made the formula much shorter so not an issue on this particular example. Since the question was about best practices, thought I'd note the limit. In the case of longer formula, the work around would probably be the AFTER INSERT trigger. – Thronk Dec 10 '13 at 17:56

score 11 · Answer 2 · answered Apr 05 '13 at 14:51

If you want to later extend someone's probation period without having to change their application date, then a computed column is NOT the way to go. Why not just use a DEFAULT constraint for both columns?

USE tempdb;
GO

CREATE TABLE dbo.foo
(
  MemberID INT IDENTITY(1,1),
  JoinDate DATE NOT NULL DEFAULT SYSDATETIME(),
  ProbationEndDate NOT NULL DEFAULT 
    DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH,0,SYSDATETIME())+3, 0))
);

INSERT dbo.foo DEFAULT VALUES;

SELECT MemberID, JoinDate, ProbationEndDate FROM dbo.foo;

Results:

MemberID    JoinDate      ProbationEndDate
--------    ----------    ----------------
1           2013-04-05    2013-06-30

(Notice I used a slightly less convulted approach to get the end of the month for two months out.)

I love that method for the last day of the month, using that instead :) — Brian, Apr 05 '13 at 15:05

score 0 · Answer 3 · answered Apr 05 '13 at 14:47

0

There's no overhead when you insert data; only when you read the column the values are computed for this column. So I'd say your approach is correct.

answered Apr 05 '13 at 14:47

AdamL

12,421
5
50
74

Best Practices for Computed Column in SQL Server

3 Answers3