Cuidados a ter com o uso de ROWCOUNT

Eu uso muitas vezes ROWCOUNT, normalmente para partir certas operações em batches.

Exemplo clássico, um delete gigante que não queremos que encha o nosso transaction log. E sim, é mais usual do que pensamos.

A questão deste post é que temos de ter muito cuidado como usamos esse rowcount, porque alguém pode mexer no nosso código mais tarde.

A sério, não percebo porque é que alguém algum dia terá de alterar o meu hiper-mega-super-bom-e-sempre-certo T-SQL, mas acontece. 🙂

E pode acontecer também que alguém não perceba porque é que o código está assim e, mais importante, não sabe o que vou mostrar.

Como normal, vamos meter a mão na massa e criar uma tabela daquelas que não interessam a ninguém.

create table LargeTable
(	id int identity
,	Col1 int not null
)
go

insert into LargeTable (Col1)
select ABS(CHECKSUM(NEWID())%1000)
from sys.objects a
cross join sys.objects b
cross join sys.objects c
go

Na minha máquina de teste temos cerca de 3,6 milhões de linhas.

Vou guardar a minha tabela original.

select *
into #t1
from LargeTable

Agora quero, por alguma razão, apagar linhas em batches de 10K.

select count(1)
from #t1
where Col1 >= 500

while @@rowcount >0
	begin
		delete top (10000)
		from #t1
		where Col1 >= 500
	end

select count(1)
from #t1
where Col1 >= 500	

Tudo apagadinho, muito bem.

Vamos repor o nosso estado inicial.

drop table #t1
go
select *
into #t1
from LargeTable

Agora aparece um bom samaritano que vai querer “logar” estes deletes… e vai usar um inócuo print.

while @@rowcount >0
	begin
		delete top (10000)
		from #t1
		where Col1 >= 500
		print 'Apagados 10000 registos'
	end

Apagámos 10000 registos e saímos do ciclo. Já fomos.

select count(1)
from #t1
where Col1 >= 500	

Mas claro que um print é parvo… afinal de contas ninguém usa isso para debug, pois não?

Que tal um insert numa tabela de log?

create table #log
(	id int identity
,	dt_ref datetime default getdate()
,	msg nvarchar(max) 
)
go
drop table #t1
go
select *
into #t1
from LargeTable
go

while @@rowcount >0
	begin
		delete top (10000)
		from #t1
		where Col1 >= 500
		insert into  #log (msg) values ('Apagados 10000 registos')
	end

Aqui está a demorar algum tempo, não?

Conseguimos fazer ainda pior, rowcount nunca será 0 porque o insert devolve sempre 1.

Vamos ver o que nos aparece nas mensagens quando matamos a nossa query.

Se formos ver a nossa tabela de Log, temos lá alguns registos…

select count(1)
from #log

“Menos mau”, conseguimos apagar tudo.

select count(1)
from #t1
where Col1 >= 500

O que quer dizer que, se deixarmos por tempo suficiente até apagamos o que queremos, mas até ao infinito e mais além.

O culpado é, claro, o insert. Esse está a devolver 1 e depois nunca vemos o 0 do delete.

insert into  #log (msg) values ('Apagados 10000 registos')
select @@rowcount

E se alguém resolver colocar por alguma razão mais obscura um begin/commit transaction?

drop table #t1
go
select *
into #t1
from LargeTable
go


while @@rowcount >0
	begin
		begin transaction
			delete top (10000)
			from #t1
			where Col1 >= 500
		commit transaction
	end

select count(1)
from #t1
where Col1 >= 500

E pimba, assim de repente, é só mais um bugzinho.

E se tomassemos algumas precauções? Pelo menos ajudava.

Neste caso vou meter por lá uma nova transação explícita e um insert na tabela de Log.

drop table #t1
go
select *
into #t1
from LargeTable
go

declare @rc int = 1
while @rc > 0
	begin
		begin transaction

			delete top (10000)
			from #t1
			where Col1 >= 500
			set @rc = @@ROWCOUNT	--recomendo também que não se deixe nenhum espaço entre o delete e o set, para evitar o "bom samaritano" 

			insert into  #log (msg) values ('Apagados 10000 registos')

		commit transaction
	end

select count(1)
from #t1
where Col1 >= 500

Agora, de novo, resultado esperado.

Espero que isto ajude alguém como me tem ajudado a mim.