Hi All
I Read about Computed Column and understood that it's used to do a calculation and have the calculation result as a field in the view.
However, i need to perform a query and return a result as a field in the view. the query that want perform can not be placed as a join in my view.
Can this be achieved using Computed Column ??
Regards.
*This post is locked for comments
Remembering what I said about this particular technique really jacking with performance if the view is of any reasonable size, I'll explain how to do it here. You have to go outside of the documentation and get your advanced developer on a little bit, but here is how to do it. Get the definition of your view in SQL Server by right clicking on the view and clicking "View Definition" from management studio.
Now, alter your view by adding a subquery within a case expression like here:
blogs.msdn.microsoft.com/.../subqueries-in-case-expressions
Make sure that it runs in sql -- that is how you know that it works. Then copy the syntax only for the case statement with the subquery inside of it. Create a computed column and put it in the '' (single quotes). A computed column only expects a string that can fit within a sql statement. You need to build a string that equals the exact expression in the sql statement that you tested earlier. You'll know it works because the view will synchronize. If it doesn't synchronize, it will warn you with the error and you can make changes.
Sadly, there is no documentation with an example like this (there are several simpler examples documented though) but I've done it a few times. Just remember the key is that the string generated is the same that you tried out with direct sql when you tested the view.
Hi Brandon
Can u give me link for that or an example to understand it
Regards.
Yes, if you write your computed column as a subquery that equivocates to an expression, but be careful. This often causes bad performance issues if the view is of any reasonable size. It's almost always better to use a temp table when faced with this scenario.
André Arnaud de Cal...
291,969
Super User 2025 Season 1
Martin Dráb
230,842
Most Valuable Professional
nmaenpaa
101,156