Calulated Fields vs UDF's

Does anyone have any suggestions on the use of Calculated fields over scalar
User Defined functions? I would think that the calulated field may be faster
than a scalar UDF but I don't know much about the mechanisms SQL uses to run
each method. Also, where might Table-valued UDF's possibly fit into this
scenario?
thank you for your time.Scalar UDFs (in SQL Server 2000) can be fairly slow, depending on the
implementation. If the same code can be placed in-line in a Select, for
example, the select will run faster than the version calling the UDF. Often
much, much faster.
I have not done a performance comparison, but with that in mind I would
expect a calculated field to run faster than a scalar UDF.
Table-valued UDFs return tables, so they are not a good fit for
high-performance operations on a column. The performance of table-valued
UDFs can be summed up this way:
In-line table valued UDF = View with parameters, therefore the UDF is
'compiled into' the plan if used in a join.
Multi-statement table valued UDF = Stored Procedure that returns a table.
If included in a join, it executes first and returns a result set.
RLF
"J. Askey" <JAskey@.discussions.microsoft.com> wrote in message
news:44F255E8-8DE8-4449-9104-C3463EEFD3F4@.microsoft.com...
> Does anyone have any suggestions on the use of Calculated fields over
> scalar
> User Defined functions? I would think that the calulated field may be
> faster
> than a scalar UDF but I don't know much about the mechanisms SQL uses to
> run
> each method. Also, where might Table-valued UDF's possibly fit into this
> scenario?
>
> thank you for your time.|||Scalar UDFs are slow when they must do a lookup (i.e., they contain a
SELECT statement). When this happens it serializes your reads such that
your queries that contain the scalar UDF behave like cursor operations.
If your UDF simply performs a calculation without any lookups, the
performance should be similar to computed columns. I think you can
create indexes on computed columns though, but I'm not totally certain.
-Alan|||Thank you RLF and Alan for your explanations. My column is indeed a general
computation on other columns in the table so it sounds like they may be
similar in performance between both methods. The one advantage of using a
scalar UDF that I have figured out is that if I have multiple tables using
this similar computation, then I simply have to make the change in one place
to effect both table queries. I suppose I could simply call the UDF in the
calculated field as well to centralize the definition.
I typically would have created a veiw with a call tothe UDF with in it and
on top of the base table columns but I wanted to careful not to spread my
data access out all over the place for columns that might naturally seem to
be contained in the base table.
Maybe I will do a little playing with this today and post some varied
results using these different approaches. It might be interesting.
"Alan Samet" wrote:

> Scalar UDFs are slow when they must do a lookup (i.e., they contain a
> SELECT statement). When this happens it serializes your reads such that
> your queries that contain the scalar UDF behave like cursor operations.
> If your UDF simply performs a calculation without any lookups, the
> performance should be similar to computed columns. I think you can
> create indexes on computed columns though, but I'm not totally certain.
>
> -Alan
>|||A few more details.
SQL Server's Query Optimizer does not understand the output distributions of
UDFs. As such, it can cause cardinality estimates in query plan generation
to be sub-optimal.
(Eventually, we may be able to improve this story in a future release)
What I tell customers now - if you can write it as scalar logic, please do
so. If you need to use a UDF, please consider a computed column over the
result of this as well so that the optimzier can create the statistics it
needs to do a good job in plan generation.
I would specifically *not* recommend the use of UDFs to perform "singleton
(index) lookups" into another table. I've seen this pattern used in a
number of deployments. While it "works", it is very hard for the query
optimizer to pick a proper join order (which means that sometimes it picks a
poor order and plan performance can become very slow). If you can represent
these as joins, I recommend that you do so.
Best of luck to you,
Conor Cunningham
SQL Server Query Optimization Development Lead
"J. Askey" <JAskey@.discussions.microsoft.com> wrote in message
news:0940C18A-9E28-4F12-B926-95B00B336D42@.microsoft.com...
> Thank you RLF and Alan for your explanations. My column is indeed a
> general
> computation on other columns in the table so it sounds like they may be
> similar in performance between both methods. The one advantage of using a
> scalar UDF that I have figured out is that if I have multiple tables using
> this similar computation, then I simply have to make the change in one
> place
> to effect both table queries. I suppose I could simply call the UDF in the
> calculated field as well to centralize the definition.
> I typically would have created a veiw with a call tothe UDF with in it and
> on top of the base table columns but I wanted to careful not to spread my
> data access out all over the place for columns that might naturally seem
> to
> be contained in the base table.
> Maybe I will do a little playing with this today and post some varied
> results using these different approaches. It might be interesting.
> "Alan Samet" wrote:
>