Eu tenho um caso em que preciso traduzir (pesquisa) vários valores da mesma tabela. A primeira maneira que escrevi, foi usando subconsultas:

SELECT
    (SELECT id FROM user WHERE user_pk = created_by) AS creator,
    (SELECT id FROM user WHERE user_pk = updated_by) AS updater,
    (SELECT id FROM user WHERE user_pk = owned_by) AS owner,
    [name]
FROM asset

Como estou usando muito essa subconsulta (ou seja, tenho cerca de 50 tabelas com esses campos), talvez seja necessário adicionar um pouco mais de código à subconsulta (por exemplo, "AND active = 1"). d coloque-os em uma função definida pelo usuárioUDF e use isso. Mas o desempenho usando esse UDF foi péssimo.

CREATE FUNCTION dbo.get_user ( @user_pk INT )
RETURNS INT
AS BEGIN 
    RETURN ( SELECT id
             FROM   ice.dbo.[user]
             WHERE  user_pk = @user_pk )
END

SELECT dbo.get_user(created_by) as creator, [name]
FROM asset

O desempenho do # 1 é inferior a 1 segundo. O desempenho do # 2 é de cerca de 30 segundos ...

Por que, ou mais importante, existe alguma maneira de codificar no SQL Server 2008, para não precisar usar tantas subconsultas?

Editar:

Apenas mais uma explicação de quando isso é útil. Essa consulta simples (ou seja, obter ID do usuário) fica muito mais complexa quando quero ter um texto para um usuário, pois tenho que me associar ao perfil para obter o idioma, com uma empresa para ver se o idioma deve ser buscado ' ed a partir daí e com a tabela de tradução para obter o texto traduzido. E para a maioria dessas consultas, o desempenho é uma questão secundária à legibilidade e manutenção.