How to update using a function

[Jim Geissman]

Take a table, where not all the columns are populated:

CREATE TABLE #T (A int, B int, C int, D int)
INSERT #T (A,B) VALUES (1,2)
INSERT #T (A,B) VALUES (3,4)
INSERT #T (A,B) VALUES (5,6)
INSERT #T (A,B) VALUES (7,8)
INSERT #T (A,B) VALUES (9,10)

The values for C and D can be computed as functions of A and B. For this
example, let's say they are twice A and three times B, respectively:

CREATE FUNCTION dbo.F(@A int,@B int)
RETURNS @Tbl TABLE (X int, Y int)
AS BEGIN
INSERT @Tbl (X,Y) VALUES (@A*2, @B*3)
RETURN
END

Now we use the function to compute the other columns:

UPDATE #T SET C=X, D=Y
FROM dbo.F(A,B)

Right? Well, no. Instead, I get this message:

Server: Msg 155, Level 15, State 1, Line 2
'A' is not a recognized OPTIMIZER LOCK HINTS option.

Any suggestions? I would like to use this structure, if possible.

Jim Geissman
Countrywide Home Loans

[Simon Hayes]

"Jim Geissman" <ji**********@countrywide.com> wrote in message
news:b8**************************@posting.google.c om...

Take a table, where not all the columns are populated:

CREATE TABLE #T (A int, B int, C int, D int)
INSERT #T (A,B) VALUES (1,2)
INSERT #T (A,B) VALUES (3,4)
INSERT #T (A,B) VALUES (5,6)
INSERT #T (A,B) VALUES (7,8)
INSERT #T (A,B) VALUES (9,10)

The values for C and D can be computed as functions of A and B. For this
example, let's say they are twice A and three times B, respectively:

CREATE FUNCTION dbo.F(@A int,@B int)
RETURNS @Tbl TABLE (X int, Y int)
AS BEGIN
INSERT @Tbl (X,Y) VALUES (@A*2, @B*3)
RETURN
END

Now we use the function to compute the other columns:

UPDATE #T SET C=X, D=Y
FROM dbo.F(A,B)

Right? Well, no. Instead, I get this message:

Server: Msg 155, Level 15, State 1, Line 2
'A' is not a recognized OPTIMIZER LOCK HINTS option.

Any suggestions? I would like to use this structure, if possible.

Jim Geissman
Countrywide Home Loans

Unfortunately, this doesn't work since you can't use column names as
parameters in this situation. In this specific case, you could simply create
C and D as computed columns:

CREATE TABLE #T (A int, B int, C as A*2, D as B*3)

Alternatively, you could use scalar UDFs:

create function dbo.F2(@i int)
returns int
as
begin
return @i*2
end

create function dbo.F3(@i int)
returns int
as
begin
return @i*3
end

update #T set C=dbo.F2(A), D=dbo.F3(B)

But scalar functions perform badly on large data sets, because they are
invoked per-row, so this might not be appropriate. If neither of these
options are useful, then the simplest solution may just be a stored
procedure which does the UPDATE.

Simon

[David Portas]

You need a scalar UDF instead of a table-valued UDF:

CREATE FUNCTION dbo.FA(@A INTEGER)
RETURNS INTEGER
AS
BEGIN
RETURN @A*2
END
GO

CREATE FUNCTION dbo.FB(@B INTEGER)
RETURNS INTEGER
AS
BEGIN
RETURN @B*3
END
GO

UPDATE #T
SET c=dbo.FA(a),
d=dbo.FB(b)

Don't store calculated results in the table if you can help it. If C and D
are always calculated from A and B then just drop the C and D columns and do
the calculation in a view or query instead.

--
David Portas
SQL Server MVP
--

[David Portas]

> Thanks for the suggestions. Unfortunately, in the real case, the function

values are a property price index and its estimated reliability, which
are computed from relatively elaborate lookups and calculations based on
zip code, effective date and property type. The key point to me is that variable names can't be used as function
parameters in a case like this. Too bad.

I'm not sure I understand why that is a problem. UDF parameters are passed
by value not by reference and in this respect my function is no different
from the one you posted - I just used different names.

If you post your function code (also DDL and sample data INSERT statements)
maybe we can help you rewrite it exactly as required. Also consider Simon's
point about the performance overhead of a UDF. Possibly you can use an
UPDATE statement, query or view to achieve the same result without using a
function.

--
David Portas
SQL Server MVP
--

[Jim Geissman]

> Don't store calculated results in the table if you can help it. If C and D

are always calculated from A and B then just drop the C and D columns and do
the calculation in a view or query instead.

Thanks for the suggestions. Unfortunately, in the real case, the function
values are a property price index and its estimated reliability, which
are computed from relatively elaborate lookups and calculations based on
zip code, effective date and property type.

The key point to me is that variable names can't be used as function
parameters in a case like this. Too bad.

Jim

[Jim Geissman]

"David Portas" <RE****************************@acm.org> wrote in message news:<kP********************@giganews.com>...

Thanks for the suggestions. Unfortunately, in the real case, the function
values are a property price index and its estimated reliability, which
are computed from relatively elaborate lookups and calculations based on
zip code, effective date and property type.

The key point to me is that variable names can't be used as function
parameters in a case like this. Too bad.

I'm not sure I understand why that is a problem. UDF parameters are passed
by value not by reference and in this respect my function is no different
from the one you posted - I just used different names.

If you post your function code (also DDL and sample data INSERT statements)
maybe we can help you rewrite it exactly as required. Also consider Simon's
point about the performance overhead of a UDF. Possibly you can use an
UPDATE statement, query or view to achieve the same result without using a
function.

Sure, I can do this with two separate function calls, each of which returns
a single scalar value. However, computing value A and value B both visit a
lot of the same lookups and computations, so it seems to me there is
duplication of effort, hence lower than optimum efficiency. Because I'm
looking at doing this approx 60 million times, I would like to use that
work to compute both values at once. I would also like to use the elegance
of a function, which makes the code easier to read. Two function calls
preserve the elegance by hiding the details, and that's what I did last
time, and will do this time. I was simply hoping to combine all that work
and retrieve both results from one call.

I don't care about the extra time required to come up with the values. I
don't do the work -- a computer does it, and computers don't complain about
unnecessary work. I will simply adjust the schedule. But it still seems
odd that I can use the function approach to get values one at a time, but
not two at a time.

Jim

[Erland Sommarskog]

Jim Geissman (ji**********@countrywide.com) writes:

Sure, I can do this with two separate function calls, each of which
returns a single scalar value. However, computing value A and value B
both visit a lot of the same lookups and computations, so it seems to me
there is duplication of effort, hence lower than optimum efficiency.
Because I'm looking at doing this approx 60 million times, I would like
to use that work to compute both values at once. I would also like to
use the elegance of a function, which makes the code easier to read.
Two function calls preserve the elegance by hiding the details, and
that's what I did last time, and will do this time. I was simply hoping
to combine all that work and retrieve both results from one call.

I don't care about the extra time required to come up with the values.
I don't do the work -- a computer does it, and computers don't complain
about unnecessary work. I will simply adjust the schedule. But it
still seems odd that I can use the function approach to get values one
at a time, but not two at a time.

There is some new syntax in the upcoming version of SQL Server that
permits you to call a table-valued function and passing column values
to it. I have not studied it in detail, though.

Anyway, this is SQL Server, and you could do one of two things that is
better than having two UDFs:

1) Return the two values in one, as a string, and then split that
string in the UPDATE statement. Sure is not going to give you
nice code, but at least you are not doing the compuation twice.

2) Keep your table-valued function, and set up a cursor on the
table to update, and update one row at a time:
UPDATE tbl
SET col3 = f.x, col4, f.y
FROM tbl t
CROSS JOIN f(@col1, @col2) f
WHERE t.keycol = @keyval
This may sound horrendeously inneffecient, but if you call an
scalar UDF in a set-based statement, you get something cursor-like
behind the scenes anyway.

Then, of course, you could devise the computation in your function so
that it works on all values in the table at once, but that may prove to
be a real challenge.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Jim Geissman]

Thanks a lot, Erland!

Jim