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!

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...

Dick Rosenberg on 2/9/2009

Hard to follow. You went too fast

Thomas Kelley on 3/23/2009

Thank you for taking the time to show this functionality.

Paul on 3/23/2009

Good useful information I can use on the job. Thank you!

sharon on 3/23/2009

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.

prajinesh on 3/23/2009

too short

keradinides on 4/1/2009

Thanks. Is the computed column evaluated even if not selected (and other columns of table selected)?

anovick on 4/2/2009

NO, the function that is referenced in a computed column but not used in the query is not executed.

Caleb Bell on 7/16/2009

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?

Tonci on 11/18/2009

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.

vipul on 9/13/2010

very usefull... Thanks ..

Sneha on 1/3/2011

I am new to UDF's for me the author was going pretty fast in explaining stuff.

Shaun Turner on 7/13/2011

use caution? or what's the point? There are many ways in SQL that you can hack around the Servers own constraints in order to do things that it wasn't designed to do, doesn't mean that we want to use it or need a demo showing us the wrong way of doing things? Sorry not trying to be difficult

MInxia on 7/13/2011

simple and clear

Dennis Allen on 7/14/2011

If the desired shape of the data was a single object that had the two output columns, would it not in all cases be better to use a view to join the needed tables to get the same results as the scalar function bound to the computed column?

Joseph on 8/2/2011

No sound

Phil Parkin on 8/17/2011

I watched it with sound turned down (so as not to annoy colleagues), but still understood - very clear.

John O'Sullivan on 8/17/2011

a slightly obtuse example

Karl R on 8/17/2011

The content was good, but the example tables and data were overly complicated. This caused me to be focusing on the structure of the data instead of the lesson being taught.

Chris Brown on 8/17/2011

The narrator went pretty fast and I didn't get a chance to review the code.

joe on 8/17/2011

Thanks

Paul on 8/17/2011

I am so glad you gave the warning at the end!

Susan Ray on 8/17/2011

The sound was difficult to hear.

Binod on 8/17/2011

Thanks ... I was not known that.... Thanks a lot dude..

domingo asuncion on 8/18/2011

Very good. More Power. - Domingo M. Asuncion

Sherry on 8/18/2011

Good information.

Pete Williams on 8/19/2011

The most important message here is this is inefficient, so use with caution, but it works.

Carlos B. Vasquez on 8/20/2011

Very useful...

Vamsi on 8/21/2011

Good

david on 9/8/2011

Good material, if you've done something similar in nature..

Sherry on 9/21/2011

Helpful.

Maurice Ivory on 1/17/2012

Interesting Video, I learned something I didn't know.