Re: Query Optimization

Tech-Archive recommends: Fix windows errors by optimizing your registry

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:

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.

--
Anith


.

Relevant Pages

  • Re: Query Optimization
    ... This seems like a typical case of UDF abuse. ... The optimizer in SQL 2000 and SQL 2005 are different and this could be one ...
    (microsoft.public.sqlserver.clients)
  • Re: Can relational alegbra perform bulk operations?
    ... learn is to refrain from coding in such a way as to gove implicit "hints" to the optimizer in the way they express their queries. ... I do cringe a little when I see all those fancy "hints" which I've never really seen the occasion to use. ... Well, as I agreed with you, one shouldn't really be fiddling with the engine, but as I remarked in an earlier post if we were to port a complex database project from say, SQL Server to Oracle, and even if we didn't use any vendor-specific features, several SQL rewriting may be warranted simply because of performance differences due to the differences of how a SQL statement is parsed & optimized between two engines. ...
    (comp.databases.theory)
  • Performance: Query optimizer producing sub-optimal result?
    ... As part of a suite of SQL statements I am trying to write a data integrity ... SELECT UID, fnCheck ... The optimizer seems to move the "DISTINCT" action in the subquery to the end ... Define a View which performs the SELECT DISTINCT part of option A. My SQL ...
    (microsoft.public.sqlserver.programming)
  • Re: Poor performance after upgrading to sql server 2005
    ... We didn't just "throw the code at the optimizer", ... have to do the same thing for SQL 2005? ... Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.setup)
  • Re: Poor performance after upgrading to sql server 2005
    ... Andrew J. Kelly SQL MVP ... problem if the optimizer could not handle subqueries but I know for a ...
    (microsoft.public.sqlserver.setup)