How to Find Blocking Queries in Transaction In SQL server?

In this article, we will learn Blocking Queries In Transaction and Except Operator and difference between Except and Not In Operator in SQL Server By Sagar Jaybhay.

Blocking Queries

Blocking Query happens because there is an open transaction.

DBCC OpenTran is a command for checking the open transaction but there is a problem it only shows the oldest active transaction. It is not going to show you an open transaction.

We write a transaction and execute that and run dbcc opentran command see below image

Below is a query which gives you all open transaction this query I found one of the blogs whose link is this.

https://stackoverflow.com/questions/4449719/is-there-a-way-to-list-open-transactions-on-sql-server-2000-database

SELECT
trans.session_id AS [SESSION ID],
ESes.host_name AS [HOST NAME],login_name AS [Login NAME],
trans.transaction_id AS [TRANSACTION ID],
tas.name AS [TRANSACTION NAME],tas.transaction_begin_time AS [TRANSACTION 
BEGIN TIME],
tds.database_id AS [DATABASE ID],DBs.name AS [DATABASE NAME]
FROM sys.dm_tran_active_transactions tas
JOIN sys.dm_tran_session_transactions trans
ON (trans.transaction_id=tas.transaction_id)
LEFT OUTER JOIN sys.dm_tran_database_transactions tds
ON (tas.transaction_id = tds.transaction_id )
LEFT OUTER JOIN sys.databases AS DBs
ON tds.database_id = DBs.database_id
LEFT OUTER JOIN sys.dm_exec_sessions AS ESes
ON trans.session_id = ESes.session_id
WHERE ESes.session_id IS NOT NULL

Except Operator in SQL server:

The Except Operator returns unique rows from the left query which are not present as a result of the right query.

1. It is introduced in SQL server 2005
2. The number and order of columns needs to be same
3. The data types need to be same

select * from Employee where EmpID between 1 and 20
except 
select * from Employee where EmpID between 10 and 100;

In the above query, we have 1 to 9 empid is not present in the second query so it only returns 1 to 9 empid result.

What is the difference between Except and Not In operator in SQL Server?

Except operator returns unique rows from left result set which are not present in right result set and Not In operator also does the same.

select * from Employee where EmpID between 1 and 20
except 
select * from Employee where EmpID between 10 and 100;

The above query is for except operator and below is a query for not in operator which does the same.

select * from Employee where (EmpID >=1 and EmpID <=20) and EmpID
not in(
select EmpID from Employee where EmpID between 10 and 100);

So what is the difference between them?

1. Except filters duplicate and returns only distinct rows from the left query that not in the right query but Not in Operator not filter the duplicates.
2. Except operator columns need to be same and also datatype but Not in operator works with a single column from outer query to a single column in Inner query.

Profile Link of GitLab Account is:- https://gitlab.com/Sagar_Jaybhay