Re: Query Optimization

Tech-Archive recommends: Speed Up your PC by fixing your registry

Anith Sen wrote:

CREATE FUNCTION fn_concat(@A varchar(255), @B varchar(255))
RETURNS varchar(255)
AS BEGIN
RETURN coalesce(@A,@B)
END

This seems like a typical case of UDF abuse. Is there a real need for an UDF
for such simple cases? Why not simply write your SQL statement as:

Well, I simplified the UDF ... in my actual application the UDF is a
little more complex.

SELECT A.a, COALESCE( A.b, A.c )
FROM A LEFT JOIN B
ON A.id = B.id ;

But why is the use of the function changing the behavior of the 2005
optimizer?

The optimizer in SQL 2000 and SQL 2005 are different and this could be one
of the processing differences due to an internal optimization routine called
constant folding, which is pretty common in most modern DBMSs.

Well - in the meantime I figured that "WITH SCHEMABINDING" in the UDF
does the magic.

--

Henning Eiben

busitec GmbH
Consultant

e-mail: eiben@xxxxxxxxxx


+49 (251) 13335-0 Tel
+49 (251) 13335-35 Fax

Rudolf-Diesel-Straße 59
48157 Münster
www.busitec.de

Sitz der Gesellschaft: Münster
HR B 55 75 - Amtsgericht Münster
USt-IdNr. DE 204607833 - St.Nr. 336/5704/1277
Geschäftsführer: Simon Böwer, Henning Eiben, Stefan Kühn, Martin Saalmann



--
.... Gentlemen: Start your debuggers...
.

Relevant Pages

  • Re: noise words, @@ERROR, and stop and resume indexing
    ... This can be resolved without making the UDF "infallible", ... Removing the noise words, as well as not passing empty or null strings ... SQL Full Text Search Blog ... if this query causes an ignored-word error ...
    (microsoft.public.sqlserver.fulltext)
  • Re: sql user defined function called on every row
    ... Half the rows had a valid m/d/y date; the other half contained blanks. ... else mylib.cvtdatemdyend thedate ... As the involuntarily assigned SQL guru at my company, that's why I always tell the developers not to use user-defined functions for batch jobs that are going to churn through hundreds of thousands, or possibly millions, of rows of data. ... For an interactive application, in which a user is going to fill up a 10 row subfile, the extra overhead from calling the UDF is probably negligible; but not for long-running batch jobs. ...
    (comp.sys.ibm.as400.misc)
  • Re: Dynamisches Füllen einer Return Table in SQL Funktion
    ... da die Erstellung einer UDF zwingend ... Gruß Thomas ... >kann man mit dynamischen SQL nicht ansprechen, ...
    (microsoft.public.de.sqlserver)
  • Re: "Error Converting varchar to int" when executing SQL Server udf
    ... >I have the following SQL Server udf: ... > create function dbo.udfIsExistingUser (@UserNodeId int = NULL) ... stored procedure -- probably by older version components that do not know ... that may work for you: since the return of your UDF is essentially ...
    (microsoft.public.vb.database.ado)
  • Re: Need Help Converting Decimal Date Values to Date Values
    ... convert these decimals to date values? ... If you're doing this in SQL, as your inquiry suggests, then you could either write a user-defined function (UDF) to do it, or you could implement the same code ... But if, on the other hand, you were using the UDF in a batch job that read through a substantial number of rows of data, you would notice a significant performance hit. ...
    (comp.sys.ibm.as400.misc)