Using the same function twice in a query (SQL Server)

Question

In SQL Server 2005, when I write a query like

SELECT m.*, a.price p1, b.price p2
FROM mytable m
LEFT JOIN products_table_1 a
ON my_hash_function(m.name) = a.hash
LEFT JOIN products_table_2 b
ON my_hash_function(m.name) = b.hash

is my_hash_function(m.name) calculated twice or just once? If twice, how can I use a variable to avoid that?

Answer 1

select  mm.*, a.price p1, b.price p2 from   
    (SELECT m.*, my_hash_function(m.name) as name
    FROM mytable m) mm
    LEFT JOIN products_table_1 a
    ON mm.name = a.hash
    LEFT JOIN products_table_2 b
    ON mm.name = b.hash

Answer 2

The execution plan shows that it indeed gets executed twice. But this holds only if the function is not deterministic. In order for a function to be deterministic, it must be defined WITH SCHEMABINDING option, and all the functions it calls must be deterministic as well. After I defined the hash function as deterministic, the plan has changed. Now it gets executed only once!

However, if you don't want to bother with that stuff, momobo's solution works just as well.