Embedded Select vs. UDF

Why is using an embedded SELECT statement faster than using an embedded UDF that has the same exact SQL code within?

Example (not syntax corrected)
Ex1:
SELECT myValue1, myValue2 FROM myTable WHERE aValue = (SELECT Value FROM someTable WHERE myIndex = 800)

is much faster than something like

Ex2:
SELECT myValue1, myValue2 FROM myTable WHERE aValue = (dbo.FN_myUDF(@.vmyIndex))

Given that dbo.FN_myUDF has the same code as the embedded select in the first example.

TIA,

KBActually I'm getting quite an opposite result:

select * from authors where au_id = (select au_id from authors where au_lname = 'Dull')

vs.

create function dbo.fn_get_au_id (
@.lname varchar(50) ) returns char(11)
as begin
declare @.au_id char(11)
select @.au_id = au_id from authors where au_lname = @.lname
return @.au_id
end
go

+

select * from authors where au_id = dbo.fn_get_au_id('Dull')

The first results in: Table 'authors'. Scan count 2, logical reads 3, physical reads 0, read-ahead reads 0.

The second yields: Table 'authors'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.|||Perhaps because SQL Server is able to take your poorly written code:

SELECT myValue1, myValue2 FROM myTable WHERE aValue = (SELECT Value FROM someTable WHERE myIndex = 800)

...and transform it into the much more efficient:

SELECT myValue1, myValue2
FROM myTable
INNER JOIN someTable on myTable.aValue = someTable.Value
WHERE someTable.myIndex = 800

...prior to executing it. The optimizer will streamline your statement whenever it can, and thus make use of any indexes on the tables and only make one pass through the subtable. Strictly following your code logic would result in a pass through someTable for every record in myTable, which is also what occurs when you call the UDF. The compiler wants none of that nonsense and fixes your code before executing it.|||Well, I just used the same sample code as you (Mr. Lindman) provided and converted it to my previously posted comparative case...and...hmmmmm...It is actually WORSE than the other two:

select * from authors a
inner join authors b
on a.au_id = b.au_id
where b.au_lname = 'Dull'

results in: Table 'authors'. Scan count 2, logical reads 5, physical reads 0, read-ahead reads 0.

In fact, even JOIN hints don't make any difference. Ironic, isn't it? ;)|||My bad. I was thinking he had the function call in his select clause. In the WHERE clause the UDF is only executed once. I get faster results with the UDF as well, though with a larger dataset I get identical results and execution plans using either the subquery method or the join method.sql