Início / dba / Perguntas / 267843
Accepted
Nishant
Asked: 2020-05-26 04:42:31 +0800 CST

Por que o SQL Server diz que não pode converter varchar em numérico?

  • 772

Eu tenho o seguinte esquema/dados no MSSQL Server 2019 ( SQLFiddle ):

CREATE TABLE products(
    idn NUMERIC(9) PRIMARY KEY
);

CREATE TABLE sales(
    idn NUMERIC(9) PRIMARY KEY,
    pid VARCHAR(50) NOT NULL,
    type VARCHAR(10) NOT NULL
);

INSERT INTO products (idn) VALUES (1);
INSERT INTO sales (idn, pid, type) VALUES (1, 1, 'number');
INSERT INTO sales (idn, pid, type) VALUES (2, 'Colgate', 'word');

salestem dados mistos, ou seja, VARCHARe NUMERIC. O filtro de transações cuida JOINcorretamente.

Por que o seguinte SELECTfalha (por algum motivo além do meu controle, a consulta gerada tem Nliterais para strings.)?

SELECT 
    * 
FROM 
    products 
    INNER JOIN sales ON products.idn = sales.pid 
    AND sales.type = N'number' 
WHERE 
    products.idn in (1);

Não vejo por que transmitir NVARCHARpara NUMERICé um problema:

SELECT CAST (N'1' as NUMERIC);

Se eu modificar um pouco a consulta, funciona:

SELECT 
    * 
FROM 
    products 
    INNER JOIN sales ON products.idn = sales.pid 
    AND sales.type = N 'number' 
WHERE
    -- Selecting the same data from `sales`.
    sales.pid in (1);

SELECT 
    * 
FROM 
    products 
    INNER JOIN sales ON products.idn = sales.pid
    -- Dropping the `N` prefix.
    AND sales.type = 'number' 
WHERE 
    products.idn in (1);
sql-server type-conversion

4 respostas

  1. Best Answer

    Ocorrerá um erro de conversão em tempo de execução quando for feita uma tentativa de converter o sales.pidvalor 'Colgate' para numeric(9) para avaliar os critérios de junção. Se isso realmente acontece ou não, depende da ordem de avaliação no plano de execução.

    Abaixo está o predicado de verificação de índice clusterizado da tabela de vendas do plano de execução literal Unicode , mostrando que a conversão ocorre antes que a condição 'number' seja avaliada:

    CONVERT_IMPLICIT(numeric(9,0),[tempdb].[dbo].[sales].[pid],0)=(1.) AND CONVERT_IMPLICIT(nvarchar(10),[tempdb].[dbo].[sales].[type],0)=N'number'

    O plano com o literal não Unicode tem a mesma forma, exceto que a ordem do predicado no operador de varredura é invertida:

    [tempdb].[dbo].[sales].[type]='number' AND CONVERT_IMPLICIT(numeric(9,0),[tempdb].[dbo].[sales].[pid],0)=(1.)

    Embora o literal não Unicode (ou parâmetro) possa solucionar o problema, a consulta ainda estará vulnerável a erros de tempo de execução. Isso pode ser resolvido com TRY_CAST, TRY_CONVERT, CASEexpressão etc., mas seria melhor corrigir o modelo de dados para garantir que apenas tipos de dados semelhantes ou compatíveis sejam comparados.

    • 2

  2. Isso ocorre porque você está usando uma coluna que contém valores de texto (pid) para unir em uma coluna numérica

    INNER JOIN sales ON products.idn = sales.pid

    Quando você faz uma junção dessas 2 tabelas, o SQL precisa comparar todas as linhas de idn com todas as linhas de pid. Para compará-los, ele precisa converter seu varchar em numérico.

    Como o SQL pode converter 'Colgate' em um número? é por isso que falha.

    Você deve ler sobre normalização de banco de dados.

    Sua coluna PID não parece boa e você provavelmente precisaria de outra tabela com um ID do produto e uma descrição do produto (onde 'Colgate' será a descrição) para que funcione como você espera.

    • 1

  3. Por que o SELECT a seguir falha (por algum motivo além do meu controle, a consulta gerada tem N literais para strings.)?

    Porque está recebendo um plano de execução que avalia

    sales.pid=1

    para a linha com

    sales.pid = 'Colgate'

    antes de avaliar o predicado

    sales.type = N'Number'

    inttem uma precedência de tipo de dados mais alta do que varcharpara executar a comparação, o 'varchar' é convertido implicitamente em int. Esta conversão falha para "Colgate".

    A maneira de bloquear a ordem de avaliação para essas duas condições é com uma expressão CASE. POR EXEMPLO

    SELECT 
    * 
FROM 
    products 
    INNER JOIN sales 
    ON  case when sales.type = N'number' and sales.pid = products.idn then 1 else 0 end = 1
WHERE 
products.idn = 1

    Isso pode causar problemas de desempenho em escala, mas esse é um dos muitos motivos pelos quais cada coluna deve ter apenas um tipo de dados.

    • 1

  4. O otimizador de consulta pode alterar a ordem em que as coisas são feitas para obter seu resultado da maneira mais rápida entre as opções que ele apresenta (dado que o resultado da consulta não é alterado).

    Sabendo disso, se você verificar o plano de execução, isso ajudará você a entender o motivo pelo qual você obtém o erro com uma consulta e não com a outra, considerando os dados de exemplo que você forneceu:

    Pergunta 1:

    SELECT 
    * 
FROM 
    products 
    INNER JOIN sales ON products.idn = sales.pid
    -- Dropping the `N` prefix.
    AND sales.type = 'number' 
WHERE 
    products.idn in (1);

    Plano de consulta

    Pergunta 2:

    SELECT 
    * 
FROM 
    products 
    INNER JOIN sales ON products.idn = sales.pid 
    AND sales.type = N'number' 
WHERE 
    products.idn in (1);

    Plano de consulta

    Predicado do plano de consulta

    A consulta 1 funcionou porque quando você removeu o N (que é usado para converter uma string para nvarchar) o SQL Server não precisou realizar uma conversão implícita de sales.typevarchar para nvarchar. Neste caso sales.pidtem uma conversão implícita em ambas as consultas pois está sendo comparada a products.idnqual tem um tipo de dado diferente. O otimizador de consultas decide que a comparação de uma coluna sem conversão implícita é mais barata, então ele começa executando o predicado sales.type = 'number'e gera um subconjunto composto pelas linhas que contém todos os valores de sales.typecomo números (e você pode converter números reais de um string cloumn sem nenhum problema). Assim, a consulta funciona.

    A consulta 2 gera um erro porque ambos os predicados de salestêm conversões implícitas e o otimizador escolheu começar de sales.pidantes de filtrar apenas as linhas que mantêm os números. Quando ele tenta converter o valor 'Colgate'em um numeric(9,0)tipo de dados, você recebe o erro.

    • 1

relate perguntas