quinta-feira, 18 de maio de 2017

RAISERROR, Transaction outcome and execution flow

Remember that when using RAISERROR inside procedures or triggers it does:
- not issue a rollback
- not interrupt the execution

For that results, when desired, use a ROLLBACK command explicitly, and a RETURN to quit from the routine. Do not misunderstand this concepts because it is so easy to think the RAISERROR will interrupt both the transaction and the execution flow.

For more detailed situations, which can apply to your case, browse on Microsoft Technet or MSDN, and google for more informations from the experts on the Internet.

Example:
Using a trigger to show the concept, we can issue any DML statements, receive an error message, and, anyway, the statements will work. No interruption. No rollback after raiserror.

CREATE TRIGGER dbo.trg_dml_testtrans
   ON  dbo.testtrans
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

print '@@trancount: ' + cast( @@trancount as varchar);

    -- Insert statements for trigger here
    raiserror('raising an error...', 16, 10);
    
print '@@trancount: ' + cast( @@trancount as varchar); 
END

insert testtrans (cola, colb) values (1, 'aaa');
insert testtrans (cola, colb) values (2, 'bbb');
update testtrans set colb='ccc' where cola=2
delete testtrans where cola=2

Each of above statements will get the errors message, even though working as expected. See one message example below, for delete statement:

@@trancount: 1
Msg 50000, Level 16, State 10, Procedure trg_dml_testtrans, Line 18
raising an error...
@@trancount: 1

Going further, the following commands show us that the delete statement (and so the insert and update) starts an implicit transaction (or automatic transaction) when executing, then commits on  after successfuly perform (auto commit). That´s because you did not see any transaction statements in the example.

print '@@trancount: ' + cast( @@trancount as varchar);
delete testtrans where cola=3
print '@@trancount: ' + cast( @@trancount as varchar);

@@trancount: 0
@@trancount: 1
Msg 50000, Level 16, State 10, Procedure trg_dml_testtrans, Line 18
raising an error...
@@trancount: 1

(1 row(s) affected)
@@trancount: 0

Try to change the trigger to issue a rollback in some condition and see for yourself how funny is to work with databases. I will give you another example:

ALTER TRIGGER [dbo].[trg_dml_testtrans]
   ON  [dbo].[TestTrans]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
   SET NOCOUNT ON;

   print '@@trancount: ' + cast( @@trancount as varchar);

   if (select colb from inserted) = 'zzz'
   begin
       raiserror('raising an error...', 16, 10);
       rollback;
       --use RETURN here to get out immediately
   end
    
   print '@@trancount: ' + cast( @@trancount as varchar); 
END

insert testtrans (cola, colb) values (7, 'zzz');

@@trancount: 1
Msg 50000, Level 16, State 10, Procedure trg_dml_testtrans, Line 19
raising an error...
@@trancount: 0
Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.

This means that the 'zzz' won't be inserted.

That's all for now, folks!

quinta-feira, 23 de março de 2017

The Autoclose effect

A valuable and simple tip for setting the autoclose property of a database and get more performance among the connections up and down. It´s in PT-BR, but you know the google translate, don't you?

https://www.dirceuresende.com/blog/sql-server-sua-instancia-esta-constantemente-com-mensagens-starting-up-database-e-databases-in-recovery-conheca-o-auto-close/

quarta-feira, 15 de março de 2017

How to change and query the retention period of CDC cleanup job [SQLSERVER]

We can use a simple way to manage the retention period of CDC cleanup routine.
Be sure to set the database you want to change because the stored procedure use the current database.
I set up 90 days retention, using 90*24*60 minutes as argument, but you should ensure your database growth will fit in you available space. Keep track of you database size for a few days before go on vacation.

use
go

SELECT DB_ID(), [retention]
  FROM [msdb].[dbo].[cdc_jobs]
  WHERE [database_id] = DB_ID()
  AND [job_type] = 'cleanup'

sp_cdc_change_job @job_type='cleanup', @retention=129600
go

SELECT DB_ID(), [retention]
  FROM [msdb].[dbo].[cdc_jobs]
  WHERE [database_id] = DB_ID()
  AND [job_type] = 'cleanup'


Source: http://sqlblog.com/blogs/allen_white/archive/2012/03/07/set-and-verify-the-retention-value-for-change-data-capture.aspx

 Anothe good tip in the source is this query, wich brings more information about CDC jobs settings:
select * from msdb.dbo.cdc_jobs

There are some more issues about this topic in the source, but for me the above content was enough to make my day. Hence, that´s all for now, folks! 

Good queries!

sexta-feira, 3 de março de 2017

Commit and Rollback are not simmetrical about @@trancount

Source: https://www.codeproject.com/Articles/4451/SQL-Server-Transactions-and-Error-Handling

That´s why it's so important to check @@trancount or track the rollbacks inside nested transactions, avoiding errors with rollback without transaction related.


Figure 1: A COMMIT always balances a BEGIN TRANSACTION by reducing the transaction count by one.


Figure 2: A single ROLLBACK always rolls back the entire transaction.

segunda-feira, 11 de julho de 2016

Recuperando TOP N linhas de uma consulta relacionada com APPLY

Neste link tem um exemplo muito útil para as situações em que é preciso recuperar as n linhas de uma tabela relacionada para cada registro da tabela mestre, usando o CROSS APPLY (ou OUTER APPLY, quando for o caso).

http://stackoverflow.com/a/2791907/3817131

Cuidado para não cair na armadilha do
SELECT * 
FROM TABLE_A AS a
LEFT JOIN (SELECT TOP 8 id, field1, field2
           FROM TABLE_b) AS b
    ON a.id = b.id
 
Pois este caso traz um jon com as TOP n *GERAL* da tabela B, não de cada linha do lado esquerdo do relacionamento.
Mesmo quando se tenta usar um JOIN/WHERE na subquery pegando a chave da tabela mestre para relacionar como parâmetro é preciso atentar para a sintaxe e para os resultados retornados.
Em alguns casos é preciso fazer uma subquery para  selecionar o resultado de um subquery mais interna.

O APPLY é uma solução elegante do TSQL e de bom desempenho, mas não é ANSI.


sexta-feira, 17 de junho de 2016

Excluindo registros repetidos


No link https://support.microsoft.com/pt-br/kb/139444 onde a Microsoft mostra como eliminar duplicidades de tuplas, podemos acrescentar outra técnica bem conhecida no exemplo, usando a própria t1. A técnica da holdkey table onde se exclui todos os registros e depois precisa reincluir pode não ser a opção ideal em bancos muito grandes, com uma tabela cheia de chaves, constraints, triggers, identity, etc.

Há uma solução padrão que usamos sem precisar reinserir os códigos distinct, pois a exclusão é feita sem precisar remover as multiplicidades, mas somente excluindo as n-1 ocorrências. O requisito é ter uma coluna que seja pelo menos uma chave candidata para fazer a exclusão da linha que não será eliminada.

ALTER TABLE t1 ADD id_1 int identity(1,1) not null;  --Criando a chave candidata, já que t1 não tem uma, e que pode ser temporária apenas para a exclusão

delete t1
where
    col2 in (SELECT col2 FROM t1 GROUP BY col2 HAVING count(*) > 1)
    and NOT id_1 in (SELECT MIN(ID_1) FROM t1 GROUP BY col2 HAVING count(*) > 1)

Em caso de múltiplas colunas, onde a cláusula IN não permite mais que uma coluna***, podemos concatenar

Exemplo real, utilizado recentemente em um cliente:
delete from dias_leitura
where codigo_cliente+cast(subcodigo as varchar) in
  (select codigo_cliente+cast(subcodigo as varchar) from dias_leitura
   group by codigo_cliente + cast(subcodigo as varchar)
   having Count(*)>1)
and not codigo in
  (select Min(codigo) from dias_leitura
   group by  codigo_cliente+cast(subcodigo as varchar)
   having Count(*)>1)

*** Isto é permitido no Oracle:  WHERE col1, col2 in (select col1, col2 from t1)...

quarta-feira, 6 de abril de 2016

Update inóquo também aciona trigger

Ao executar um update em que a condição não seja atendida, 0 linhas serão afetadas (inóquo, i.e., sem efeito), e os triggers associados serão executados, mesmo que nenhum dado seja modificado. O único problema aqui é executar alguma ação que conte com o acionamento do trigger sem testar se realmente algum dado foi modificado.

Acabei de eliminar um bug que estava enviando email sobre a exclusão de consumidor porque em qualquer condição que não era uma inclusão ele entrava no ELSE e montava o corpo do email. O bug é que em caso de alteração ou de falha de update o email era enviado, e nestes casos, vazios.

Exemplo:
if exists(select * from inserted)
{código do insert/update}
else
{código do update/delete e dos casos acima, que pode ser um bug ou algo especial}

Uma possível solução:
if exists(select * from inserted)
{código do insert/update}
else if exists(select * from deleted)
{código do update/delete e dos casos acima, que pode ser um bug ou algo especial}

Um código independente pode estar fora do IF..ELSE.

That's all for now, folks!