SQL Server Performance Blog where milliseconds matter

Hi guys, Hello everyone and welcome again! Today I wanted to talk to you about some basic concepts related to relational databases that are absolutely worth knowing ! . We will talk about  Transactions , lock  and also about a particular type of lock called  deadlock . I'll tell you some theory but don't worry: as usual I will try to be as clear as possible ! You are ready? So happy reading!!! What is a transaction? A transaction is a sequence of operations which, if completed without errors, produces a change of state in our database . For example: BEGIN TRAN INSERT INTO TABLE_A (CAMPO1, CAMPO2) VALUES ('VAL1','VAL2') INSERT INTO TABLE_B(CAMPO1,CAMPO2,CAMPO3) VAULES ('VAL1','VAL2','VAL3') COMMIT or INSERT INTO TABLE _A (CAMPO1, CAMPO2) VALUES ('VAL1','VAL2') In the first case we are talking about explicit transaction , in the second of  implicit transaction . …and therefore w...

Hi Guys, Welcome back! After a dip in the maze of the logic of the SQL Server optimizer , today we talk about the command XACT_ABORT . We will see how important this command is when we deal with transactions. Do not worry, I will try to explain in the simplest way possible! XACT_ABORT The XACT_ABORT is an option which can have only two possible values: ON and OFF.   You can simply set it's value with the T-SQL command: SET XACT_ABORT ON OR SET XACT_ABORT OFF If XACT_ABORT is set to ON then if a T-SQL command throws an error then the transaction will be rollbacked . Suppose of having a trigger triggered by an update on the table A If an istruction inside the trigger fail then also the update on the table will be rollbacked . Yes, this is the concept. Let's have this example with XACT_ABORT set to ON Suppose we have a TABLE_A table with the field CAMPO1 non nullable with a 0 value . SET XACT_ABORT...