Início / dba / Perguntas / 339824
Accepted
John K. N.
Asked: 2024-05-27 19:44:27 +0800 CST

ALTER TABLE ALTER COLUMN <nome> falhou porque um ou mais objetos acessam esta coluna

  • 772

Introdução

Recentemente, durante uma atualização de um aplicativo, recebi a seguinte mensagem de erro:

Msg 5074 Level 16 State 1 Line 1
The statistics 'BreakingStuff' is dependent on column 'TaskText'.
Msg 4922 Level 16 State 9 Line 1
ALTER TABLE ALTER COLUMN TaskText failed because one or more objects access this column.

Exemplo de mensagem de erro baseada em análise

Esta foi uma estatística criada manualmente em uma coluna de uma tabela. Na verdade, havia várias estatísticas criadas manualmente referenciando a coluna em questão.

Gambiarra

Depois de descartar as estatísticas da tabela, executar a atualização e recriar as estatísticas, tudo estava funcionando bem.

Análise

Comecei com uma simulação trivial em db<>fiddle para ver se conseguia reproduzir o problema. Depois de alguns ajustes (trocadilhos), descobri que a mensagem de erro só é acionada se eu reduzir o tamanho da coluna.

por exemplo varchar(50)-->varchar (40)

Aqui está o db<>fiddle .

Os únicos avisos que encontrei no Microsoft Learn foram:

  1. Modificar o tipo de dados de uma coluna que já contém dados pode resultar na perda permanente de dados quando os dados existentes são convertidos para o novo tipo. Além disso, o código e os aplicativos que dependem da coluna modificada podem falhar. Isso inclui consultas, visualizações, procedimentos armazenados, funções definidas pelo usuário e aplicativos cliente. Observe que essas falhas ocorrerão em cascata. Por exemplo, um procedimento armazenado que chama uma função definida pelo usuário que depende da coluna modificada pode falhar. Considere cuidadosamente quaisquer alterações que você deseja fazer em uma coluna antes de fazê-la.

    Referência: Modificar colunas (Microsoft Learn | SQL)

Eu estava ciente de que reduzir o tamanho da coluna possivelmente resultaria em perda de dados, mas não foi o caso, pois os dados eram todos menores que varchar(40).

Questões

  1. Por que reduzir o tamanho da coluna resulta em erro devido às estatísticas criadas manualmente?
  2. Por que aumentar o tamanho de uma coluna não resulta em erro nas estatísticas criadas manualmente?
sql-server

2 respostas

  1. Best Answer

    O SQL Server 2022 tem uma nova opção ao criar estatísticas que podem automatizar isso conforme a documentação (ênfase minha):

    AUTO_DROP = {ATIVADO | OFF } Aplica-se a: SQL Server 2022 (16.x) e versões posteriores e Banco de Dados SQL do Azure, Instância Gerenciada de SQL do Azure

    Antes do SQL Server 2022 (16.x), se as estatísticas fossem criadas manualmente por um usuário ou por uma ferramenta de terceiros em um banco de dados do usuário, esses objetos de estatísticas poderiam bloquear ou interferir nas alterações de esquema que o cliente pudesse desejar.

    A partir do SQL Server 2022 (16.x), a opção AUTO_DROP é habilitada por padrão em todos os bancos de dados novos e migrados. A propriedade AUTO_DROP permite a criação de objetos estatísticos em um modo tal que uma alteração de esquema subsequente não seja bloqueada pelo objeto estatístico, mas em vez disso as estatísticas sejam eliminadas conforme necessário. Dessa forma, as estatísticas criadas manualmente com AUTO_DROP ativado se comportam como estatísticas criadas automaticamente.

    Observação

    Tentar definir ou desativar a propriedade Auto_Drop em estatísticas criadas automaticamente pode gerar erros. As estatísticas criadas automaticamente sempre usam a eliminação automática. Alguns backups, quando restaurados, podem ter esta propriedade configurada incorretamente até a próxima atualização do objeto de estatísticas (manual ou automaticamente). No entanto, as estatísticas criadas automaticamente sempre se comportam como estatísticas de eliminação automática. Ao restaurar um banco de dados para SQL Server 2022 (16.x) de uma versão anterior, é recomendado executar sp_updatestats no banco de dados, definindo os metadados adequados para o recurso de estatísticas AUTO_DROP.

    • 5

  2. Colunas que possuem estatísticas anexadas criadas manualmente não podem ter suas propriedades modificadas sem primeiro eliminar o objeto de estatísticas – isso é para garantir que o objeto de estatísticas reflita com precisão o conteúdo da coluna. Quando uma coluna de estatísticas criada manualmente é detectada, o SQL Server retorna uma mensagem de erro informando “ALTER TABLE ALTER COLUMN falhou porque um ou mais objetos acessam esta coluna”.

    Escrevi um post sobre isso aqui

    Curiosamente, se o SQL Server tiver criado automaticamente um objeto de estatísticas em uma coluna e você modificar posteriormente essa coluna, você não receberá esse erro. O SQL Server descarta silenciosamente o objeto de estatísticas e modifica a coluna. O objeto de estatísticas criado automaticamente não é recriado automaticamente até que seja executada uma consulta que precise do objeto de estatísticas.

    Aumentar o tamanho da coluna é uma operação somente de metadados que na verdade não altera nenhum dos dados existentes, portanto, o objeto stats ainda é válido.

    Diminuir o tamanho de uma coluna pode significar que os valores serão truncados, o que pode invalidar as estatísticas.

    • 4

relate perguntas