Início / dba / Perguntas / 117931
Accepted
Collin Dauphinee
Asked: 2015-10-14 13:51:24 +0800 CST

Posso criar uma coluna computada que requer entrada para selecionar?

  • 772

Tenho um cenário em uma aplicação muito antiga e muito grande, onde tenho uma tabela representando um tipo de recurso:

CREATE TABLE resource (resource_id INT, name NVARCHAR(4000))

Essa tabela é selecionada em centenas de locais diferentes, incluindo procedimentos armazenados e SQL dinâmico no código do aplicativo.

Uma equipe atualizou recentemente o nome deste recurso para ser localizado, e sua abordagem é bastante direta. Há uma nova tabela contendo os nomes localizados e um ID de idioma 'padrão' na resourcetabela, para quando o nome não estiver localizado para o idioma solicitado:

-- Foreign keys omitted
ALTER TABLE resource ADD default_language_id INT
CREATE TABLE resource_local (resource_id INT, language_id INT, name NVARCHAR(4000))

A maioria dos procs tem um @user_language_idparâmetro, portanto, a lógica para escolher o nome a ser retornado é simples: pegue resource_local.namecorrespondência language_id = @user_language_idse existir, caso contrário, resource_local.namecorresponda language_id = resource.default_language_idse existir, caso contrário pegue resource.name.

Infelizmente, isso transforma a lógica para selecionar o nome correto em algo assim:

SELECT ISNULL(ISNULL(exact.name, default.name), res.name)
FROM resource res
LEFT JOIN resource_local exact ON exact.resource_id = res.resource_id 
    AND exact.language_id = @user_language_id
LEFT JOIN resource_local default ON default.resource_id = res.resource_id
    AND default.language_id = res.default_language_id
WHERE res.resource_id = @resource_id

Todas as centenas de locais que tentam selecionar resource.nameestão tendo que ser atualizadas com essa lógica, o que tornou esse projeto um esforço massivo de toda a organização, pois cada equipe precisa atualizar seu SQL para usar essa lógica. Isso também causa problemas de manutenção, pois qualquer novo desenvolvedor que lida com essa tabela precisa saber que não pode usar apenas a namecoluna.

Agora é tarde demais, mas estou curioso: existe alguma maneira melhor de abordar isso, de modo que selecionar a namecoluna de resourceapenas 'faça a coisa certa' com base na @user_language_idvariável (se existir)?

sql-server computed-column

2 respostas

  1. Best Answer

    Não tenho certeza se é possível fazer isso para que nenhuma das referências à resourcetabela precise ser alterada. Parece que o fato de que um language_idé necessário é uma mudança fundamental que todo código de chamada precisará estar ciente.

    No entanto, é possível projetar isso de forma que o recurso possa ser consultado de uma das maneiras simples a seguir. Uma dessas opções pode ter sido uma mudança mais fácil de fazer e manter em tantos lugares diferentes.


    Função com valor de tabela

    Usando uma função com valor de tabela embutida , podemos fornecer a seguinte sintaxe.

    SELECT resource_id, language_id, name
FROM dbo.resourceTVF(@resource_id, @language_id) r

    Aqui está um exemplo de como criar a função. É essencialmente a mesma consulta da sua pergunta, mas com o alias defaultalterado para def( defaulté uma palavra-chave do SQL Server).

    -- Create the Table-Valued Function
CREATE FUNCTION dbo.resourceTVF (@resource_id INT, @user_language_id INT)
RETURNS TABLE 
AS
RETURN
SELECT @resource_id AS resource_id,
  @user_language_id AS language_id,
  ISNULL(ISNULL(exact.name, def.name), res.name) AS name
FROM dbo.resource res
LEFT JOIN dbo.resource_local exact ON exact.resource_id = res.resource_id 
    AND exact.language_id = @user_language_id
LEFT JOIN dbo.resource_local def ON def.resource_id = res.resource_id
    AND def.language_id = res.default_language_id
WHERE res.resource_id = @resource_id
GO


    Visão

    Você pode renomear a resourcetabela (por exemplo, para resource_base) e criar uma resourceexibição para fornecer a seguinte API:

    SELECT resource_id, language_id, name
FROM dbo.resource
WHERE resource_id = @resource_id
  AND language_id = @language_id

    A principal desvantagem é que a definição de exibição precisa de CROSS JOINtodos os recursos e idiomas antes de usar a aplicação LEFT JOINaos recursos locais e padrão. Mesmo assim, este será um plano bastante eficiente com 4 buscas singleton, supondo que você tenha os índices adequados.

    CREATE VIEW dbo.resource WITH SCHEMABINDING AS
SELECT res.resource_id,
  lang.language_id,
  ISNULL(ISNULL(exact.name, def.name), res.name) AS name
FROM dbo.resource_base res
CROSS JOIN dbo.languages lang
LEFT JOIN dbo.resource_local exact ON exact.resource_id = res.resource_id 
    AND exact.language_id = lang.language_id
LEFT JOIN dbo.resource_local def ON def.resource_id = res.resource_id
    AND def.language_id = res.default_language_id
GO


    roteiro completo

    Aqui está um script completo onde implementei essas duas propostas, carreguei uma pequena quantidade de dados falsos e executei alguns casos de teste. Pelo menos para esses casos de teste, ambas as abordagens produzem os resultados desejados e usam um plano baseado em loop-seek.

    Acho que a função com valor de tabela embutida é provavelmente a abordagem que eu tentaria primeiro. Observe que você pode usar CROSS APPLYpara "juntar" à função com valor de tabela se precisar de mais de um recurso por vez.

    • 4

  2. Eu definitivamente segundo a abordagem de função de valor de tabela em linha (iTVF) de @Geoff e ia sugerir isso eu mesmo, mas ele chegou antes de mim ;-).

    Vou apenas acrescentar que parece que ter 2 níveis de "padrão" parece um pouco complicado. Quero dizer, não entendo por que o default_language_idfoi adicionado à resourcetabela. Parece permitir que vários recursos em uma determinada página sejam provenientes de vários idiomas. Acho que é mais consistente para o usuário final se você simplesmente tiver o recurso baseado em localidade/LCID na nova tabela e, se não for encontrado, encontre o padrão na resourcetabela. Mas voltar lá primeiro apenas para obter um padrãolanguage_id? Acho que, a longo prazo, isso causará mais problemas do que resolverá. Se você vai ter um padrão, deve haver um padrão. Ou você encontra o nome do recurso no LCID desejado ou não o encontra e volta para o padrão (embora provavelmente não deva deixar isso acontecer, pois uma página que contém inglês (da esquerda para a direita) e hebraico ou árabe (idiomas da direita para a esquerda) pode ser um pouco confuso ;).

    • 1

relate perguntas