Fala pessoal,
Nesse post vou compartilhar mais um Caso do Dia a Dia, dessa vez explicando a mensagem de erro abaixo ao utilizar um CURSOR.
“Could not complete cursor operation because the table schema changed after the cursor was declared”
Caso do Dia a Dia – Erro ao utilizar um Cursor:
De forma bem resumida, o CURSOR pode ser utilizado em LOOPs no SQL Server. Existem quatro tipos de cursores:
- forward-only
- static
- keyset
- dynamic
Mais detalhes no link abaixo da documentação oficial da Microsoft:
https://learn.microsoft.com/en-us/sql/relational-databases/cursors?view=sql-server-ver16
Vamos iniciar o nosso cenário de testes criando e populando uma tabela com quatro registros. Também criei uma Primary Key na coluna ID.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
USE Teste -- POPULA TABELA DROP TABLE IF EXISTS tb_test CREATE TABLE tb_test ( ID INT IDENTITY, NOME VARCHAR(100) ) ALTER TABLE tb_test ADD CONSTRAINT PK_tb_test PRIMARY KEY(ID) INSERT INTO tb_test VALUES (‘Luiz’), (‘Vitor’),(‘França’),(‘Lima’) SELECT * FROM tb_test |
No teste 1, mostro a sintaxe básica para percorrer cada registro do CURSOR e exibir o resultado.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- TESTE 1 - USANDO O CURSOR DECLARE @ID INT, @NOME VARCHAR(100) DECLARE TESTE_CURSOR CURSOR FOR SELECT * FROM tb_test OPEN TESTE_CURSOR FETCH NEXT FROM TESTE_CURSOR INTO @ID , @NOME WHILE @@FETCH_STATUS = 0 BEGIN SELECT @ID AS ID, @NOME AS NOME FETCH NEXT FROM TESTE_CURSOR INTO @ID , @NOME END CLOSE TESTE_CURSOR; DEALLOCATE TESTE_CURSOR; |
Até aqui tudo bem! Agora propositalmente vou incluir um WAITFOR DELAY dentro do cursor para fazer a execução demorar alguns segundos e em paralelo (em uma outra sessão) vou executar um REBUILD nessa mesma tabela “tb_test”. Ao fazer isso, vamos ter o erro abaixo:
“Could not complete cursor operation because the table schema changed after the cursor was declared”
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
-- TESTE 2 - GERANDO O ERRO NO CURSOR DECLARE @ID INT, @NOME VARCHAR(100) DECLARE TESTE_CURSOR CURSOR FOR SELECT * FROM tb_test OPEN TESTE_CURSOR FETCH NEXT FROM TESTE_CURSOR INTO @ID , @NOME WHILE @@FETCH_STATUS = 0 BEGIN SELECT @ID AS ID, @NOME AS NOME FETCH NEXT FROM TESTE_CURSOR INTO @ID , @NOME WAITFOR DELAY '00:00:10' END CLOSE TESTE_CURSOR; DEALLOCATE TESTE_CURSOR; /* -- RODAR EM PARALELO - EM UMA OUTRA SESSÃO USE Teste ALTER INDEX PK_tb_test ON tb_test REBUILD */ |
O erro acontece porque o CURSOR utiliza a tabela “tb_test” e durante a sua execução ela teve uma alteração (nesse caso, foi o REBUILD). Com isso, quando o SQL Server vai executar a próxima iteração do CURSOR ele falha!
No link abaixo, temos uma ótima resposta do Aaron Bertrand, onde ele sugere alterar o tipo do CURSOR para STATIC, pois dessa forma será feita uma cópia temporária dos dados para que o cursor não seja impactado por outras alterações.
“but static makes a copy of the data so that the cursor isn’t impacted (well, doesn’t care about) any of those changes.”
Portanto, ao alterar o tipo para CURSOR STATIC, não vamos ter mais o erro, pois como foi explicado, ele faz uma cópia temporária dos dados e as alterações na tabela não irão afetá-lo. Lembrando que ao fazer isso estaremos consumindo mais recursos OK.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-- TESTE 3 - UTILIZANDO O CURSOR STATIC DECLARE @ID INT, @NOME VARCHAR(100) DECLARE TESTE_CURSOR CURSOR STATIC FOR SELECT * FROM tb_test OPEN TESTE_CURSOR FETCH NEXT FROM TESTE_CURSOR INTO @ID , @NOME WHILE @@FETCH_STATUS = 0 BEGIN SELECT @ID AS ID, @NOME AS NOME FETCH NEXT FROM TESTE_CURSOR INTO @ID , @NOME WAITFOR DELAY '00:00:10' END CLOSE TESTE_CURSOR; DEALLOCATE TESTE_CURSOR; |
Por fim, o próprio link da documentação oficial da Microsft recomenda utilizar um WHILE ao invés do CURSOR nesses casos em que a tabela possui uma Primary Key, para evitar uma sobrecarga no CURSOR com a cópia temporária dos dados.
“In some scenarios, if there’s a primary key on a table, a WHILE loop can be used instead of a cursor, without incurring in the overhead of a cursor.”
https://learn.microsoft.com/en-us/sql/relational-databases/cursors?view=sql-server-ver16
“Ah Luiz, então o CURSOR é SEMPRE PIOR do que um WHILE?”
Não estou dizendo isso, mas mostrei um caso em que o WHILE poderia ser melhor. Para outras situações pode ser que o mais recomendado seja um CURSOR. Você precisa entender bem cada cenário e as limitações de cada um deles para fazer a melhor escolha. Eu particularmente prefiro o WHILE. =)
Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!
Me siga no LinkedIn e YouTube para ficar por dentro das novidades.
Abraço,
Luiz Vitor França Lima
Consultor SQL Server
Que erro curioso… Quem trabalha com diversos cenários no dia a dia, como o pessoal da Power Tuning, depara com situações bizarras… É um aprendizado contínuo!
Em casos de somente leitura dos dados do cursor, geralmente a opção FAST_FORWARD é o suficiente para evitar a situação citada e melhorar o desempenho, conforme você mesmo cita em seu artigo.
Mas melhor ainda se substituir o cursor por tratamento de conjunto de linhas (“dataset”), né?
Exatamente José, cada dia uma surpresa diferente kkk. Sempre que for possível evitar utilizar CURSOR / WHILE e não precisar fazer algo linha a linha seria o melhor cenário mesmo.
Mas quando não tiver jeito, tem que entender se tem uma Primary Key envolvida e talvez seja melhor utilizar um WHILE, igual comentei no post.
Obrigado pelo comentário!
Abraço,
Luiz Lima