P: n/a

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  
Share this Question
P: n/a

"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 perrow, 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  
P: n/a

You need a scalar UDF instead of a tablevalued 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
  
P: n/a

> 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
  
P: n/a

> 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  
P: n/a

"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  
P: n/a

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 tablevalued 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 tablevalued 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 setbased statement, you get something cursorlike
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  
P: n/a

Thanks a lot, Erland!
Jim   This discussion thread is closed Replies have been disabled for this discussion.   Question stats  viewed: 31076
 replies: 7
 date asked: Jul 20 '05
