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

Cursor In RDBMS By Sagar Jaybhay 2020

In this article we will understand cursor in rdbms in our case we show example on SQL Server By Sagar Jaybhay. Also we will understand Merge statement in SQL Server and rerunnable SQL scripts and How to create a stored procedure with an optional parameter?

Cursors In RDBMS

In a relational database management system takes into consideration then it would process the data in sets inefficient manner.

But when you have a need to process the data row by row basis then the cursor is the choice. The cursor is very bad at performance and it should be avoided and also you can replace the cursor with join.

Different Types of Cursors In RDBMS

Their are four types of cursors in rdbms which are listed below

1. Forward only
2. Static
3. Keyset
4. Dynamic

The cursor is loop through each record one by one so that’s why it’s performance is not good.

declare @empid int
declare @deptid int
declare @fullname varchar(200)

declare empcurose cursor for 
select EmpID,full_name,DepartmentID from Employee

open empcurose

fetch next from empcurose into @empid,@fullname,@deptid

while(@@FETCH_STATUS=0)
begin

print 'EmpID '+cast(@empid as varchar(10))+ ' Name '+cast(@fullname as varchar(100)) + ' deptid '+cast(@deptid as varchar(100))
fetch next from empcurose into @empid,@fullname,@deptid
end
close empcurose
deallocate empcurose

deallocate empcurose

This line is used to deallocate all resources which are allocated for that cursor.

What is rerunnable SQL scripts?

A re-runnable SQL script is a script that runs multiple times on the machine will not throw any kind of error.

For example, if you use create table statement to create a table then use if not exist in create a statement so it will not throw an error.

How to create a stored procedure with an optional parameter?

create procedure searchemployee
@name varchar(10)=null,
@deptid int=null,
@gender varchar(10)=null
as
begin

if(@name is not null)
print 'i am in name '+cast(@name as varchar(20))
select * from tblEmp where [name]=@name;
return;

if(@deptid is not null)
print 'i am in deptid '+cast(@deptid as varchar(20))
select * from tblEmp where deptid=@deptid;
return;

if(@gender is not null)
print 'i am in gender '+cast(@gender as varchar(20))
select * from tblEmp where geneder=@gender;
return;
print 'i m here '+cast(@gender as varchar(20))+' '+cast(@deptid as varchar(20)) +' '+cast(@name as varchar(20))
select * from tblEmp

end

execute searchemployee @deptid=2

Simply pass default values to stored procedure variables.

Merge statement In SQL server

Merge statement is introduced in SQL server 2008 it allows to insert, update, deletes in one statement. It means there is no need to use multiple statements for insert update and delete.

In this, if you want to use merge statement you need to 2 tables

1. Source table- it contains the changes that need to apply to the target table.
2. Target table- this is the table that requires changes insert, update, delete.

Merge statement joins the target table to source table by using a common column in both tables based on how you match up we perform insert, update and delete.

---

Transaction Link: https://www.codementor.io/@sagarjaybhay18091988/transaction-in-sql-server-155l4qr7f4

---

Subqueries & Correlated Subqueries SQL Server 2020

In this article Sagar Jaybhay explain how to write Subqueries and Correlated Subqueries in SQL Server and What to choose for Performance Subquery or Join?

Subqueries In SQL Server:

Subqueries are enclosed in parenthesis. Subquery also called an inner query and the query which enclosed that inner query is called an outer query. Many times subqueries can be replaced with joins.

select * from Employee where DepartmentID not in (select distinct DepartmentID from Department)

Another example

select Department_Name,(select count(*) from Employee where DepartmentID=d.DepartmentID) from Department as d;

The above query is an example of use subquery in the select list. The above result can be achieved using join also see below query

select d.Department_Name,COUNT(e.empid) as empcount from Department d
join Employee e on e.DepartmentID=d.DepartmentID
group by d.Department_Name
order by empcount;

According to MSDN, you can nested up to 32 levels.

Columns present in subqueries can not be used in the outer select list of a query.

Correlated Subqueries:

If our subquery depends on the outer query for its value then it is called Correlated subqueries. It means subquery depends on outer subquery/ Correlated subqueries are executed for every single row executed by outer subqueries.

A correlated subquery can be executed independently

select distinct Department_Name,(select count(*) from Employee where DepartmentID=d.DepartmentID group by DepartmentID) as empcount from Department as d order by empcount;

What to choose for Performance Subquery or Join?

According to MSDN, there is no big difference between queries that use sub-queries and joins.

But in some cases, we need to check the performance and Join produces better performance because the nested query is must be processed for each result of the outer query. In such cases, JOIN will perform better.

In general, JOIN works faster as compared to subqueries but in reality, it will depend on the execution plan generated by SQL Server. If the SQL server generates the same execution plan then you will get the same result.

Transaction Link: https://www.codementor.io/@sagarjaybhay18091988/transaction-in-sql-server-155l4qr7f4

How to handle errors in SQL Server By Sagar Jaybhay

In this article we will understand How to handle errors in SQL Server By Sagar Jaybhay.

Handle Error

In SQL Server 2005 they introduced try/catch block in SQL server likes C# and Java.

In SQL Server 2000 they have syntax -- @@Error

In SQL Server 2005 they have introduced – try/catch.

In SQL server the variables which are starting with @@ symbols are called global variables but hey are not variables but working like variables but they are similar to a function.

Throw an error in SQL Server we have a function, Raiserror(error_message, severity level, state)

In this Raiserror function, the first parameter is error_message which we want to display. Like throw keyword in C#.

The second parameter is Error Severity level- which is most cases is 16 means the user can resolve this error.

The third Parameter is State: It is a number between 1 to 255 but Raiserror will generate state between 1 to 127.

@@ERROR is a system function that contains non zero value if there is no error else it has 0 value.

@@ERROR is cleared and reset on each statement of execution.

Try/ Catch:

Whatever we can do with @@ERROR we can achieve it by using a try-catch block. You can write any number of statement inside the try block and if any error occurred then control directly moves in the catch block and the rest of the statement in try blocks are the skip. If no error will occur then the control bypass/skip the execution of the catch block.

Errors that are trapped in the catch block are not returned to calling function for that you need to use Raiserror function.

You can use system function which gives more information about the error and this can be called inside catch block only.

To write code in try-catch block use below syntax here

Begin Try
//-- Your code is here
End Try
Begin Catch
//-- Your code is here

End Catch

In SQL server to get more information SQL server provides functions for that which is described below

1. Error_Number() : display how many errors occurred
2. Error_message() : It returns the message of error    
3. Error_Procedure(): returns the name of the stored procedure or trigger where an error occurs
4. Error_State(): returns the error state regardless of how many times it is run, or where it is run within the scope of the CATCH block
5. Error_Severity(): returns the error severity value of an error, regardless of how many times it runs or where it runs within the scope of the CATCH block
6. Error_Line (): returns the line number at which the error occurred. 

Above all of these functions are run inside the context of the catch block. Outside the catch block, it will return null.

GitHub: https://github.com/Sagar-Jaybhay

Route Parameter Constraints In Asp.Net Razor Pages

In this article we will understand Route Parameter Constraints In Asp.Net Razor Pages by Sagar Jaybhay

Route Parameter Constraints

Route Constraints is a
mechanism where it filters out or restricts unwanted route parameters to
reaching out to PageModel methods.

To add route constraints we need to add constraints in route template data. For example, if we want our id only accepts numeric value then for this is below code.

@page "/employee/view/id:int"

Now we want to apply constraints with optional parameters then we need to use below syntax.

@page "/employee/view/id:int?"

Below is a list of the table this kind of constraints we can apply in the route template.

Constraint	Description	Example
alpha	Matches uppercase or lowercase Latin alphabet characters (a-z, A-Z)	title:alpha
bool 1	Matches a Boolean value.	isActive:bool
int 1	Matches a 32-bit integer value.	id:int
datetime 1	Matches a DateTime value.	startdate:datetime
decimal 1	Matches a decimal value.	cost:decimal
double 1	Matches a 64-bit floating-point value.	latitude:double
float 1	Matches a 32-bit floating-point value.	x:float
long 1	Matches a 64-bit integer value.	x:long
guid 1	Matches a GUID value.	id:guid
length	Matches a string with the specified length or within a specified range of lengths.	key:length(8) postcode:length(6,8)
min	Matches an integer with a minimum value.	age:min(18)
max	Matches an integer with a maximum value.	height:max(10)
minlength	Matches a string with a minimum length.	title:minlength(2)
maxlength	Matches a string with a maximum length.	postcode:maxlength(8)
range	Matches an integer within a range of values.	month:range(1,12)
regex	Matches a regular expression.	postcode:regex(^[A-Z]2\d\s?\d[A-Z]2$)

We can apply more than
one constraint at a time for this use below syntax.

For example, we want to Id whose minimum value is 1 and max value 100 can accept

@page "/employee/view/id:min(1):max(100)"

How to create Custom Constraints in Asp.Net Razor pages?

There is a five-step method to create Custom Constraints in Asp.net razor pages.

• First, we required the inbuilt interface for creating a constraint and below is Interface for that.

public interface IRouteConstraint : IParameterPolicy
 
 bool Match(HttpContext httpContext, IRouter route, string routeKey, RouteValueDictionary values, RouteDirection routeDirection);
 

• The second is to create a class and implement this interface in our newly created class and your required logic in match method which returns true or false.

public bool Match(HttpContext httpContext, IRouter route, string routeKey, RouteValueDictionary values, RouteDirection routeDirection)
 
 if(values[routeKey].ToString().Trim().Length!=0)
 
 var regex = new Regex("^[a-zA-Z0-9]4$");
 if (regex.IsMatch(values[routeKey].ToString()))
 return true;
 
 return false;
 

• The now third step to add a custom constraint to our route template for this use below code.

@page "/employee/view/id:custom"

• When we run our application we get an error which is shown below.

---

An unhandled exception occurred while processing the request.

InvalidOperationException: The constraint
reference 'custom' could not be resolved to a type. Register the constraint
type with 'Microsoft.AspNetCore.Routing.RouteOptions.ConstraintMap'.

Microsoft.AspNetCore.Routing.DefaultParameterPolicyFactory.Create(RoutePatternParameterPart parameter, string inlineText)

• To overcome this error we need to add this constraint in configureservice method using ConstraintMap method

public void ConfigureServices(IServiceCollection services)
 
 services.AddRazorPages();
 services.AddSingleton<IEmployeeRepos, DBRepository>();
 services.Configure<RouteOptions>(option =>
 
 option.LowercaseUrls = true;
 option.LowercaseQueryStrings = true;
 option.AppendTrailingSlash = true;
 option.ConstraintMap.Add("custom", typeof(CustomConstraints.custom));
 );
 

By doing this our application work perfectly fine but when we pass more than 4 character id it will give below output.

Using custom constraints it will not throw an error but it shows 404 not found.

Now we need to understand each parameter in the Match method.

bool Match(HttpContext httpContext, IRouter route, string routeKey, RouteValueDictionary values, RouteDirection routeDirection);

Name	Explnation
httpContext	An object that encapsulates information about the HTTP request.
route	he router that this constraint belongs to.
routeKey	The name of the parameter that is being checked.
values	A dictionary that contains the parameters for the URL.
routeDirection	An object that indicates whether the constraint check is being performed when an incoming request is being handled or when a URL is being generated.

---

GitHub:- https://github.com/Sagar-Jaybhay/AspNerRazorPages

---

Route Parameter In Razor Pages Sagar Jaybhay

In this article we will understand Route Parameter In Razor Pages By Sagar Jaybhay.

Route Parameter:

Another way to pass value from is route template. If you see the above image you have seen that the id parameter is passed as a query string parameter. But we want to pass as route template to do this we need to add below code in our Display template of Details .cshtml page.

@page "id"

By doing so our URL will become like below

Here are URL contains id but not as a QueryString parameter. If you want to make the id parameter as optional value then you need to add a Question mark after this like below.

@page " id?"

Now we want to change the route URL you can do so by using the route template right now in our application we go to details view and we want our URL like https://localhost:44316/employee/details/view/a101/  to achieve this modify route template like below and you can achieve this.

@page "view/id"

The output is shown in the below image. By doing so /view and /id is appended to default URL and URL looks like below

But this URL so long we want some custom URL like https://localhost:44316/employee/view/a101/ To achieve this URL we need to modify route template like below

@page "/employee/view/id"

The output of this shown in below image and we can remove details name in URL

If you want to include as many parameters uses below syntax the name present in curly braces are route parameters.

@page "/employee/view/id/name"

Here keep in mind Model binder binds route parameter to id parameter in OnGet() method.

But if you want to access the Id parameter in the Display template you need to create a public property and assign value to it in the OnGet method.

Now this property we use in our Display Template so our output looks like below

So here we need to assign route parameter value to public property in OnGet() method why because by default route parameter support for OnPost()
the method only but we want to access for OnGet() means get call also so we need to use below syntax for that and no need to assign value in OnGet() method. Code for this is written below

 [BindProperty(SupportsGet = true)]
 public string ID get; set; 

The bind property attribute binds route parameter value to ID property in Page template public property. By default, BindProperty Bind value to this public property only for Post request so we pass SupportsGet flag to true to support for Get() call also.

GitHub:- https://github.com/Sagar-Jaybhay/AspNerRazorPages

Query String Parameters In Asp.Net Razor 2020

In this article we will understand Query String Parameters its significance. How to Use Query String Parameters in Asp.Net Razor Pages ?

Query String Parameters

If we want to pass data from the Display template to Code
behind file which is Page Model in our case then we can use the
asp-data-parameter_name tag helper to pass data from the display template to
the Page Model class. This parameter catches in OnGet or OnPost method.

Now we have to achieve functionality like when we click
on View button we can get in detail information about Employee in table format.
For this, we need to create one Details Razor page in our Employee folder which
resides under Page folder.

First, we need to pass data from View for that use below the line of code.

 <tr><td><a class="btn btn-primary" asp-page="/Employee/Details" asp-route-ID="@emp.ID">View</a></td></tr>

Now we need to cacth this Id in OnGet method for that we pass parameter in OnGet() method.

public void OnGet(string id)
 

 

In this Model binding maps id parameter value to OnGet() method id parameter. And by default id is pass as query string parameter in URL.

We don’t have any method which can get Employee Information by using Id so we need to create this in Interface and then we need to implement this in our DBRepository class as we implement this interface in this class.

The code for Interface and DbRepository have shown below.

 public interface IEmployeeRepos
 
 IEnumerable<Employee> GetAllEmployees();
 Employee GetEmployee(string Id);
 

 public class DBRepository: IEmployeeRepos
 
 private List<Employee> _empList;
 public DBRepository()
 
 _empList = new List<Employee>() 
 new Employee() Dept=Department.HR,Email="sagarjaybhay@gmail.com",ID="A101",Name="Sagar Jaybhay",Photopath=@"sagar jaybhay.png",
 new Employee()Dept=Department.IT,Email="rani@hotmail.com",ID="A201",Name="Rani",Photopath=@"rani.jpg" ,
 new Employee() Dept=Department.Testing,Email="raja@gmail.com",ID="A301",Name="Raja",Photopath=@"ram.png",
 new Employee() Dept=Department.Testing,Email="raghu@gmail.com",ID="A401",Name="Ragu",Photopath=""

 ;
 


 public IEnumerable<Employee> GetAllEmployees()
 
 return this._empList;
 

 public Employee GetEmployee(string Id)
 
 return this._empList.Where(s => s.ID == Id).SingleOrDefault();
 
 

Here is one note: When you use the anchor tag element with an asp-page tag helper then not use default href attribute if you do so you will get an error. Following is the error.

An
unhandled exception occurred while processing the request.

InvalidOperationException: Cannot override the 'href' attribute for <a>. An <a> with a specified 'href' must not have attributes starting with 'asp-route-' or an 'asp-action', 'asp-controller', 'asp-area', 'asp-route', 'asp-protocol', 'asp-host', 'asp-fragment', 'asp-page' or 'asp-page-handler' attribute.

GitHub: https://github.com/Sagar-Jaybhay/AspNerRazorPages