Início / dba / Perguntas / 118666
Accepted
cocogorilla
Asked: 2015-10-21 12:40:55 +0800 CST

impedir operador de inserção de índice clusterizado em exibição indexada não qualificada

  • 772

Alguém sabe uma solução alternativa para isso? Essencialmente, o procedimento armazenado força um operador de inserção na exibição indexada, mesmo que as linhas não sejam qualificadas. Como resultado, há um erro de conversão. No entanto, para ad hocs, o sql elimina corretamente a exibição da consideração.

Considere o seguinte esquema:

create table testdata (
    testid int identity(1,1) primary key
  , kind varchar(50)
  , data nvarchar(4000))
go
create view integer_testdata with schemabinding
as
select cast(a.data as int) data, a.kind, a.testid
  from dbo.testdata a
 where a.kind = 'integer'
go
create unique clustered index cl_intdata on integer_testdata(data)
go
create procedure insert_testdata
(
    @kind varchar(50)
  , @data nvarchar(4000)
)
as
begin
  insert into testdata (kind, data) values (@kind, @data)
end
go

Todos estes funcionam:

insert into testdata (kind, data) values ('integer', '1234');
insert into testdata (kind, data) values ('integer', 12345);
insert into testdata (kind, data) values ('noninteger', 'noninteger');
exec insert_testdata @kind = 'integer', @data = '123456';
exec insert_testdata @kind = 'integer', @data = 1234567;

Isso falha:

exec insert_testdata @kind = 'noninteger', @data = 'noninteger';

Uma comparação dos "planos de execução estimados":

insert into testdata (kind, data) values ('noninteger', 'noninteger'): insira a descrição da imagem aqui

exec insert_testdata @kind = 'noninteger', @data = 'noninteger': insira a descrição da imagem aqui

sql-server

1 respostas

  1. Best Answer

    Obrigado por fornecer um script completo para recriar o problema.

    Eu testei com o SQL Server 2014 Express.

    Quando eu adiciono OPTION(RECOMPILE)funciona:

    ALTER procedure [dbo].[insert_testdata]
(
    @kind varchar(50)
  , @data nvarchar(4000)
)
as
begin
  insert into testdata (kind, data) 
  values (@kind, @data)
  OPTION(RECOMPILE);
end

    Quando eu executo isso no SSMS:

    exec insert_testdata @kind = 'noninteger', @data = 'noninteger';

    Eu recebo esta mensagem:

    (1 row(s) affected)

    e uma linha é adicionada à tabela.

    Qual versão do SQL Server você está usando? Lembro vagamente que nas versões anteriores a 2008 isso OPTION(RECOMPILE)se comportava um pouco diferente.

    Estou trabalhando com uma estrutura de dados existente e preciso de uma pesquisa rápida em valores inteiros exclusivos armazenados no subconjunto de nvarchar(4000), um filtro em outra coluna define esse subconjunto de linhas.

    Nesse caso, pode ser melhor usar o índice filtrado em vez da exibição indexada:

    CREATE UNIQUE NONCLUSTERED INDEX [IX_DataFiltered] ON [dbo].[testdata]
(
    [data] ASC
)
WHERE ([kind]='integer')

    O otimizador deve usar esse índice quando o WHEREfiltro da consulta corresponder exatamente à WHEREcláusula do índice.

    Sim, aqui o índice está na nvarcharcoluna, o que pode não ser a melhor coisa, especialmente se você juntar esta tabela com uma intcoluna de outra tabela ou tentar filtrar valores nesta coluna usando intvalues.

    Outra variante que vem à mente é a coluna computada persistente que converte nvarcharem int. Em essência, é muito semelhante à sua exibição, mas os nvarcharvalores persistentes convertidos intsão armazenados na mesma tabela, não em um objeto separado.

    CREATE TABLE [dbo].[testdata](
    [testid] [int] IDENTITY(1,1) NOT NULL,
    [kind] [varchar](50) NULL,
    [data] [nvarchar](4000) NULL,
    [int_data]  AS (case when [kind]='integer' then CONVERT([int],[data]) end) PERSISTED,
PRIMARY KEY CLUSTERED 
(
    [testid] ASC
))


CREATE UNIQUE NONCLUSTERED INDEX [IX_int_data_filtered] ON [dbo].[testdata]
(
    [int_data] ASC
)
WHERE ([kind]='integer')

    Com esta configuração, tentei usar seu procedimento armazenado original para inserir linhas e funcionou mesmo sem OPTION(RECOMPILE).

    Na verdade, parece que a principal razão pela qual a coluna persistida acima funciona é que eu uso CASE. Se eu adicionar CASEà definição de sua visão, o procedimento armazenado funcionará sem OPTION(RECOMPILE).

    create view integer_testdata2 with schemabinding
as
select 
    case when a.kind='integer' then CONVERT(int, a.data) end as data
    , a.kind, a.testid
from dbo.testdata a
where a.kind = 'integer'
go
    • 6

relate perguntas