SQL Server Performance & Troubleshooting – Where Milliseconds Matter 🚀

Hi Guys, Welcome back! Today we have a little insight into the world of triggers . We will see in which ways to detect if the values ​​of a column have changed Ready? Go! The UPDATE function In a trigger, the UPDATE (FIELD) function is the fastest way to check whether the value of the <FIELD> field has changed. It is faster because it only parses the T-SQL command to be executed even if the field value is not actually changed. Example: UPDATE TABELLA SET FIELD = FIELD always return TRUE Reading the tables INSERTED and DELETED Alternatively, to check if the value of a field has been changed, you can query the two tables INSERTED and DELETED by putting them in JOIN: IF EXISTS( SELECT I.ID FROM INSERTED I JOIN DELETED D ON I.ID = D.ID WHERE I.FIELD <> D.FIELD) BEGIN END That's all for Today! Luca You may also be interested in: SQL Server: Transazioni, Lock e Deadlock. Un po di teor...

Hi guys, Welcome back! Today a light post about a question that has been asked to me several times. The question is: how to  search for text inside a trigger or a stored procedure ? We have two different ways. A first way using a system view called syscomments: SELECT obj.xtype, text , * FROM sysobjects obj JOIN syscomments com on obj.id = com.id WHERE TEXT like '%text_that_i_am_searching_for%' A second way using a system view called sql_module: SELECT o. name AS Object_Name, o. type_desc , m. definition FROM sys.sql_modules m JOIN sys.objects o ON m. object_id = o. object_id WHERE m. definition Like '%text_that_i_am_searching_for%' ;   Another question is? What differences do we have in using one way instead of the other? Which solution is better? You have to consider that the system view sys.syscomment is obsolete and will be removed sooner or later. So don't use it in production enviroiments. The sys.sql_m...

Hi Guys, Today light article in which I want to tell you a short note on optimization of triggers. Enjoy the reading! Carissimi Lettori, Oggi articolo leggero leggero in cui voglio raccontavi una breve nota sulla ottimizzazione dei trigger. Buona lettura! Introduction Let's say right away that a good rule to apply is to avoid the execution of T-SQL statements when actually their execution goes to insert / update any row. Suppose we have two tables called TABLE_A and TABLE_B. Table_A has an id_table_B field that points to TABLE_B.ID We then have a simple insert trigger done as below. ALTER TRIGGER [dbo].[TR_INS_TABLE_A] on [dbo].[TABLE_A] for insert AS begin Update b set b.campo1 = -1 from inserted I join TABLE_B b on i.id_table_B = b.id where b.campo1 = 0 end If the TABLE_A.id_table_B field accepts the null values ​​we will have that some rows of TABLE_A will have a value in the id_table_B field, other rows of the same table will ...

Ciao a tutti!   Oggi parliamo di trigger e più nello specifico della funzione UPDATE() e più in generale di come ottimizzare questi automatismi che SQL Server mette a disposizione. Partiamo dalla funzione UPDATE () per parlare delle metatabelle INSERTED e DELETED . Buona lettura!   La funzione UPDATE()   Già, ma che cos’è la funzione UPDATE()? La funzione UPDATE(NOMECOLONNA) , che si utilizza dentro ad un trigger restituisce l’informazione relativa al fatto che il valore della campo NOMECOLONNA abbia cambiato o meno valore. Vediamo un esempio creandoci due semplici tabelle uguali tra di loro CREATE table TABELLA_A (id int identity (1,1), CODICE VarChar (40), DESCR VarChar (40)) CREATE table TABELLA_B (id int identity (1,1), CODICE VarChar (40), DESCR VarChar (40)) Adesso creiamo un trigger in update sulla tabella_A per ottenere questo comportamento: Se cambia il codice nella tabelle_A allora cambierà il codice nella tabella_B CREATE TRI...