|
|
|
|
|
|
|
|
|
Using a UDF in a Computed Columns
Computed columns are a way to add virtual columns to a table and can be a powerful technique (when used appropriately). In this video function wizard Andy Novick shows you how to go beyond simple expressions in a computed column by using a user defined function as part of the definition. Worth watching!
|
|
|
|
Duration:
|
1
mins
57
secs
|
|
Skill Level:
|
100
|
|
Rating:
|
4.23
out of 5
|
|
Publish Date:
|
January 28, 2009
|
|
|
|
About the Author
|
|
Andrew Novick is a SQL Server consultant building business solutions with the Microsoft tools. His expertise in database design, T-SQL coding, SQL Server security, database performance tuning, and database partitioning can be a valuable asset to many projects. Andrew Novick also offers training and mentoring services to relevant projects. His writing on computing can be seen at his web site ht...
|
|
References
|
|
Comments
|
|
Hard to follow. You went too fast
|
|
Thomas Kelley on
3/23/2009
Thank you for taking the time to show this functionality.
|
|
|
Good useful information I can use on the job. Thank you!
|
|
|
10x
|
|
Bill Portman on
3/23/2009
This has huge performance implications as well as hard to manage and potentially problematic hidden code. Definately, use caution.
|
|
|
too short
|
|
|
Thanks. Is the computed column evaluated even if not selected (and other columns of table selected)?
|
|
|
NO, the function that is referenced in a computed column but not used in the query is not executed.
|
|
|
Would it probably be better to do a JOIN and use a view instead of this technique? When would it be better to use a computed column instead of JOINing?
|
|
|
Although, a computed column based on a scalar udf call may easily bring performace problems, it is a really awesome to know about this feature. This feature is completely unexpected and looks like the way to go, if for example, this scalar udf, which is returning a value, is making a select on a table of at most 100 rows or less. If the table this udf is calling doesnt change, you might want to make this udf deterministic. Just awesome!! Thank you.
|
Must Be Logged In
|
|
|
|
|