I'm on a team that's going to start a new project, to replace an old MS Access app, that's the front end to a SQL Server 2012 database. We intend to add some new tables. One of the tables I'd like to add would include a computed field. I've known about computed fields for a long time but haven't used them before. This would be a great use case for it.
But the thing that I'm worried about is whether the SQL 2012 database even supports computed fields. I've looked up computed fields, which brought me to this page. However, all that page says is that computed fields are supported in those SQL Server versions that support it.
So, does SQL Server 2012 support computed fields?
Kindest Regards, Rod Connect with me on LinkedIn.
Yes. I just set the compatibility of a database to 2012 and ran the code in the link you supplied and it worked fine. I believe that SQL 2016 is the lowest version with documentation from Microsoft, so that is why it is the lowest version you can choose. It does not necessarily mean the feature was introduced in SQL 2016.
March 4, 2022 at 8:00 pm
Just curious - but if you are starting a new project to convert this MS Access database to SQL Server, why have you chosen SQL Server 2012? Especially when that product hits end of life in July this year?
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
March 6, 2022 at 3:27 pm
Excellent question. The same question could be used for other things that happen where I work. For example, every new project we start we use .NET Framework 4.5.2, which last year I pointed out a .NET Blog post clearly stating that .NET Framework 4.5.2 is going out of support on April 26 of this year. In view of that clear declaration by Microsoft that .NET Framework is going out of support, they bury their heads deeper in the sand. One of my colleagues told me that he thought Microsoft would do a better job supporting .NET Framework 4.5.2, than they will supporting the newly released .NET 6, with respect to providing security updates to .NET Framework 4.5.2 and not .NET 6. Either this guy has read the blog post and is actively denying what's written in it and lying about it to everyone else, or he hasn't read any more of it than the blog post's heading, then choose to imagine his own fantasy of events. I've seen this same behavior applied to other technologies which have gone out of support. Support ends, but of course that doesn't mean the technology stops, so they continue using it. Time passes, then something happens like a failure which can't be fixed, or a security breach happens. Suddenly everyone's in a panic. Screams of, "WE MUST GET THIS FIXED NOW!!!!!!!!!", echos through the halls. Sometimes innocent people get punished because someone must suffer for what's happened. And lots more money is spent than would have happened if action were taken earlier when the old technology was still under support.
I have no answer to the question of why build a new app on technology that will shortly go out of support. I've been in this position for 7 years and have witnessed this pattern of behavior happen a couple of times. No one learns from those mistakes. And what frustrates me the most is no one listens to warnings. Occasionally, such as happens when I warn about .NET Framework 4.5.2 going out of support, I only gain the enmity of a coworker.
Kindest Regards, Rod Connect with me on LinkedIn.
March 7, 2022 at 3:38 am
Ah, be careful now. If you use a formula for the backbone of your computed "field" (they usually referred to as a computed or calculated "column") and it has any form of scalar UDF in it, you've just relegated your queries to always being single threaded even if the column isn't addressed by by query.
Here are two really good articles on the subject and, yes... I've proven through testing that, as usual, Mr. Ozar is right again/still.
https://www.brentozar.com/archive/2020/10/using-triggers-to-replace-scalar-udfs-on-computed-columns/
My only issue with the trigger workaround occurs when the table is audited UNLESS you use an INSTEAD OF trigger, which is a bit of a PITA, IMHO.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply