Using the same function twice in a query (SQL Server)
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 onc开发者_如何学运维e? If twice, how can I use a variable to avoid that?
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
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.
精彩评论