Por que o SQL tenta converter linhas que são excluídas pela minha cláusula where?

A visualização não está sendo usada, pois está sendo "otimizada" pelo otimizador de consulta. Você pode ver isso observando o plano de consulta:

Você pode criar uma exibição materializada indexada e usar a NOEXPANDdica de tabela na consulta de destino para evitar essa otimização.

Um exemplo:

USE tempdb;

IF OBJECT_ID(N'dbo.TestView', N'V') IS NOT NULL
BEGIN
    DROP VIEW dbo.TestView;
    DROP TABLE dbo.Test;
END
CREATE TABLE dbo.Test 
(
    TableKey int
    , TestField nvarchar(24)
);
GO

Crie uma visualização, com SCHEMABINDING:

CREATE VIEW dbo.TestView 
WITH SCHEMABINDING
AS
    SELECT 
        t.TableKey
        , t.TestField
    FROM
       dbo.Test t
    WHERE ISNUMERIC(t.TestField) = 1;
GO

Crie um índice na exibição:

CREATE UNIQUE CLUSTERED INDEX PK_TestView
ON dbo.TestView (TableKey);
GO

Insira os dados do teste:

INSERT INTO dbo.Test
select 0,        '0'
union select 1,  'Rejected'
union select 2,  'Unlinked'
union select 0,  '0'
union select 3,  '1'
union select 162,'1000'
union select 16, '10000'
union select 17, '10010'
union select 18, '10011'
union select 19, '10012'
union select 20, '10031'
union select 21, '10041'

Consulte a visualização:

SELECT * 
FROM dbo.TestView WITH (NOEXPAND)
WHERE Testfield = 1000

Resultados:

╔══════════╦═══════════╗
║ TableKey ║ TestField ║
╠══════════╬═══════════╣
║ 162 ║ 1000 ║
╚══════════╩═══════════╝

O plano de consulta:

Observe a exclamação triangular no nó SELECT no plano de consulta acima - isso é um aviso:

A conversão de tipo na expressão (CONVERT_IMPLICIT(int,[tempdb].[dbo].[TestView].[TestField],0)) pode afetar "CardinalityEstimate" na escolha do plano de consulta, a conversão de tipo na expressão (CONVERT_IMPLICIT(int,[tempdb] .[dbo].[TestView].[TestField],0)=CONVERT_IMPLICIT(int,[@1],0)) pode afetar "SeekPlan" na escolha do plano de consulta

O aviso de conversão de tipo pode ser eliminado se modificarmos a visualização assim:

CREATE VIEW dbo.TestView 
WITH SCHEMABINDING
AS
    SELECT 
        t.TableKey
        , TestField = TRY_CONVERT(int, t.TestField)
    FROM
       dbo.Test t
    WHERE ISNUMERIC(t.TestField) = 1;

Agora, a view retornará a TestFieldcoluna digitada como um inteiro, e como estamos usando NOEXPANDem combinação com dados persistentes, a conversão de tipo implícita é eliminada:

Como outras respostas explicaram, o problema é com a INSERTinstrução e - quando isso é corrigido - com a exibição sendo otimizada.

O resultado é que a consulta where Testfield = 1000tentará converter TestFieldpara numérico (devido às regras de precedência de tipo de dados ) para todas as linhas e, portanto, todos os valores da coluna.

A ordem de execução é muito difícil de forçar, pois o otimizador é livre para reescrever uma consulta e não fornece garantias sobre o tempo, a ordem ou o número de avaliações de expressões escalares.

Uma maneira de contornar isso é usar uma CASEexpressão. Ainda não é absolutamente garantido evitar esse tipo de problema, mas quase sempre funciona. Testado em sqlfiddle.com :

CREATE VIEW dbo.TestView 
AS
  SELECT  
    TableKey,
    TestField = CASE WHEN IsNumeric(TestField) = 1 THEN TestField END
  FROM
    dbo.Test
  WHERE
    IsNumeric(TestField) = 1 ;

Veja também esta resposta de Aaron Bertrand para uma explicação mais detalhada: CTE Error (nvarchar to numeric) e a sugestão de usar o mais robusto TRY_CONVERT()se você estiver em uma versão recente o suficiente.

Ok - pode não ter ficado claro na minha pergunta, mas a principal coisa que eu queria fazer era consultar minha visão sem precisar usar aspas.

Consegui isso graças às versões mais recentes do SQL e da try_convertfunção. Se eu adicionar isso à minha visão no campo em questão, posso consultá-lo com sucesso sem usar aspas.

Violino mostrando a mudança.

Curiosamente, o violino não retorna resultados - mas meu código real está funcionando dessa maneira, então vou chamar de vitória.

Não tem nada a ver com a vista. Tem a ver com o sindicato. O SQL Server está digitando sua união para (int, int)ele precisa digitar para (int, nvarchar(24)). Embora contra-intuitivos, os tipos em sindicatos são sempre interessantes. No seu exemplo, a inserção está realmente falhando.

Para corrigir isso, apenas certifique-se de consultar o TestField como se fosse uma coluna de texto (porque foi isso que você fez), e no insert verifique se está digitando corretamente os valores para não confundir o SQL Server.

insert into dbo.Test
select 0,   '0'
union select 1, 'Rejected'
union select 2, 'Unlinked'
union select 0, '0'
union select 3, '1'
union select 162,   '1000'
union select 16,    '10000'
union select 17,    '10010'
union select 18,    '10011'
union select 19,    '10012'
union select 20,    '10031'
union select 21,    '10041'
;
select * from dbo.Test
where Testfield = '1000';

Eu criei uma versão ligeiramente modificada do seu link sqlfiddle.com .

Os dados são preenchidos corretamente, podem ser selecionados usando a mesma instrução da exibição, mas a seleção na exibição em que id= 1000 ainda falha.

Por quê? A culpa é do otimizador.

O otimizador vê através da visão e basicamente trata:

SELECT TableKey, TestField
  FROM dbo.TestView
 WHERE TestField = 1000
;

Como se fosse:

SELECT TableKey, TestField
  FROM dbo.Test
 WHERE IsNumeric(TestField) = 1
   AND TestField = 1000
;

Quando vai otimizar, tem que decidir qual limitará melhor os dados retornados - e a cardinalidade de TestFieldé muito melhor do que IsNumeric(TestField).

Eu admito, eu estou supondo lá. Ele pode ignorar a função porque apenas um valor numérico pode corresponder ou simplesmente avaliá-lo em segundo lugar porque não pode determinar facilmente a cardinalidade do resultado da função sem examinar a tabela e executar a função.

Ainda assim, a realidade básica é que o otimizador não filtrará as linhas não numéricas primeiro, mesmo que seja isso que você deseja que ele faça.

Se você transformou a exibição em uma exibição indexada e usou a NOEXPANDdica de tabela, poderá obter os resultados esperados; o otimizador deve se recusar a ir direto para a tabela e usar o índice na visão para o que precisa.

A solução mais simples é evitar a conversão implícita. Se você não deixar de lado a conversão para SQL, então você tem o controle do que está acontecendo.

Antes do SQL 2012, forçar sua entrada para uma string (usando '1000'em vez de 1000) é provavelmente a solução mais simples.

A partir do SQL 2012, você pode usar TRY_CONVERT(veja a última consulta no sqlfiddle ). Isso retorna um valor convertido onde pode e um NULL onde não pode. Claro, nesse ponto, você poderia acabar com a vista.

Então:

1. O SQL Server não trata as exibições como tabelas e pode reorganizá-las para que a WHEREcláusula da exibição não seja avaliada antes da WHEREcláusula da consulta.
2. Não confie em conversões implícitas para fazer o que você quer do jeito que você quer.

Você precisa curto-circuitar o teste e não o valor usando casealgo assim:

  SELECT TableKey, TestField
  FROM dbo.Test
 WHERE 
   case when isnumeric(testfield)=1 then testfield else null end = 1000

Colocando

case when isnumeric(testfield)=1 then testfield else null end as newTestField

na visão e testar contra isso no seu selecttambém funcionaria.

Vamos ser fundamentais.

• Uma visão é - da maneira que você fez - avaliada em tempo de execução.
• Para verificar o filtro, ele deve comparar todas as linhas, sejam elas numéricas ou não. Não há "otimização" na exibição. A exibição forçaria uma conversão de cada linha para uma verificação numérica.
• O mesmo com sua consulta. A consulta torna a visualização redundante - a verificação é, portanto, eliminada logicamente. Mas ao comparar com o valor numérico, você AINDA deve converter todas as linhas.

Armadilha típica de iniciante. Função sobre campo (em geral) não significa otimização (a menos que use recursos como um índice sobre um campo calculado). A conversão neste cenário forçou a verificação de tabela E a conversão de cada linha. Tabela ruim e design de dados voltando para mordê-lo.

Como diz o RDFozz , sua melhor maneira é forçar uma comparação de string ( LIKE '1000') que é nativa de seus dados.