Difference Between Stored Procedure And Function In SQL - Interview Question

Introduction
In this article we will learn what is the difference between Stored Procedure and SQL function. Most asked Interview question the difference between function and stored procedure.

Previous Updates
In previous articles we have learnt Inner Join using lambda expression. .Arrange Distinct Elements Of A List In Ascending Order Using LINQ 

To increase performance of our database, we need to concern with several aspects during database design. One of the most useful operations for performing those operations as well as maintaining efficiency is as follows:

Why Stored Procedure

Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. But Function is compiled and executed every time when it is called which makes execution faster. Instead of retrieving data from sql queries we used Stored procedures or proc.

Difference between StoredProcedure and Function

• Procedure can return zero, single or multiple values whereas Function must return a value 

• Procedures can have input/output parameters whereas Function only have input parameters. 

• Procedures can not be called from function whereas Functions can be called from Procedure.

• Procedure allows DML (Insert/Update/Delete) and also SELECT statement whereas Function only allow Select statement.

• Procedure can not be used with Where/ having sql conditional statement whereas function can be used with conditional statements.

• Procedure can have transaction whereas Function can't.

• Procedure can have try catch for Exception-Handeling whereas Function can't.

Read More

Trigger In SQL Server - Insert Update Delete In Single Trigger With Example

Introduction
In this article we will learn what is trigger . How to manage Insert, Update and Delete operation in trigger. Insert Update and Delete with in Trigger.

Previous Updates
In previous articles we have learnt  Transaction Commit and Rollback in sql server with example.What is Lock and how to achieve lock on sql table. What is Blocking and Deadlock In SQL. Encrypt ConnectionString in Web.config.

What is Trigger
As per Microsoft official defination A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. 

Table Structure

Create Table TestEmployee ( ID INT IDENTITY(1,1) PRIMARY KEY , Name VARCHAR(50), Department VARCHAR(50)) Create Table TestEmployee_History (EmpHistID INT IDENTITY(1,1) PRIMARY KEY, EmpID INT , Name VARCHAR(50), Department VARCHAR(50), ActionType VARCHAR(10))

Trigger For Insert Update Delete

CREATE TRIGGER trg_TestEmployee_IUD ON TestEmployee AFTER INSERT, UPDATE, DELETE AS BEGIN DECLARE  @ID INT,                @Name VARCHAR(50),                @Department VARCHAR(50) ---- Get data from inserted/ updated SELECT @ID= ID,           @Name = Name,           @Department = Department            FROM inserted  ---- Get data from deleted               SELECT @ID= ID,                      @Name = Name,                      @Department = Department                                    FROM deleted   -- Insert Case    IF EXISTS( SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)      BEGIN              INSERT INTO TestEmployee_History(EmpID, Name, Department, ActionType)               Values( @ID, @Name, @Department, 'Insert')     END          -- Update Case    IF EXISTS( SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)     BEGIN             INSERT INTO TestEmployee_History(EmpID, Name, Department, ActionType)               Values( @ID, @Name, @Department, 'Update')     END               -- Delete Case        IF EXISTS( SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)        BEGIN                       INSERT INTO TestEmployee_History(EmpID, Name, Department, ActionType)            Values( @ID, @Name, @Department, 'Delete')        END END

In this trigger all update insert delete operations of table captured separately . 

Now Insert a Record in TestEmployee table- 

 When you insert a record on TestEmployee table then in message window you will see 1 Row(s) affected two times. This is because of trigger which inserts same record in History table.

 Update a Record in TestEmployee table-

Now Delete a Record in TestEmployee table-

In these images you can easily see the trigger works. On actionType column all three operations recorded.
Hope this article will helpful for you. If any issue then please let us know in comment section.

 

Read More

Transaction Commit and Rollback In SQL Server/ Why do i need Transactions In SQL Server

Introduction
In this article we will learn commit and rollback commands in sql server. What is transaction in sql. Why do we need commit and rollback in sql server. Can we rollback a table after performing operation on that table.

Previous Updates
In previous articles we have learnt What is Lock and how to achieve lock on sql table. How high quality content affects your Website. Why every business needed digital Marketing and Why digital marketing Required. Authorization in Asp.Net. 

Database Structure
Before starting the Transaction commit and rollback , at first i am showing you my database structure. Insert and table creation script here.

CREATE TABLE TestEmpDetail ( ID INT Identity(1,1) Primary Key, Name Varchar(50), Address Varchar(250)) Insert Into TestEmpDetail Values( 'Jack', 'UK') Insert Into TestEmpDetail Values( 'Mark', 'USA') Insert Into TestEmpDetail Values( 'Rus', 'Eng') Insert Into TestEmpDetail Values( 'Jow', 'UAE')

After inserting data Look at the table by using Select command.

Commit In SQL
Commit is used for permanent changes in table. When commit perform in any query then the changes will not rollback.
Example  : 

BEGIN TRAN test UPDATE TestEmpDetail set Name ='Bill' where ID=2 COMMIT TRAN test

Here test is the Transaction name and we update the TestEmpDetail table for Id 2. And these changes are committed so this change will not rollback.

Rollback In SQL
As its name suggest Rollback command is used for revert the changes done in table but before commit. The data which is committed or inserted into DB will not reverted or rollback.
SYNTAX

BEGIN TRAN tranName --Command for operation ROLLBACK TRAN tranName

BEGIN TRAN test UPDATE TestEmpDetail set Name ='Bill1' where ID=2 UPDATE TestEmpDetail set Name ='Jaff' where ID=3 SELECT * FROM TestEmpDetail

Now you can see after the Test transaction table has two new updated entries . For id 2 and 3 named changes in SQL.
Now i will perform  rollback  statement on test named Transaction. After rollback the newly updated table data has lost. Because we only performed Begin Trans and no commit transaction done . 

ROLLBACK TRAN test;

Read More

Blocking and Deadlock In SQL Server With Example

Introduction
In this article we will learn what is blocking in sql and how can we see a live demo ofblocking using sql server. Also we will learn Deadlock in sql server with example. Difference between deadlock and blocking with example.

Previous Updates
In previous articles we have learnt what is connection pooling in c# . What is Lock and how to achieve lock on sql table. How high quality content affects your Website. Why every business needed digital Marketing and Why digital marketing Required. Authorization in Asp.Net. 

Blocking In SQL
Blocking occurs when a transaction tries to request the resource and use it which has already locked by other transaction. Then the blocked transaction remains blocked until the first transaction not release the lock.

Means if you have a table named TestTable1. And you made a transaction (like insert/update query) on TestTable1  (Transaction here is transaction1 for table TestTable1) and at the same time or after acquiring the lock by transaction1 again the request came from any Transaction which will be transaction 2 or other  then it will wait for the transaction1 to complete.
Until the transaction1 will not complete no other transaction happend on TestTable1.

-- This is Transaction1 BEGIN TRAN UPDATE EmpDetail SET Address = 'Eng' WHere ID= 4

After executing the above lines ,execute the same query on second instance of sql server.

-- This is Transaction2 BEGIN TRAN UPDATE EmpDetail SET Address = 'Eng' WHere ID= 4

When you run the Transaction2 it will Executes query and will be on processing until the commit will not done for Transaction1. When Transaction1 commit done then autometically Transaction2 will executed.

-- This commit Transaction release the Transaction1 Commit Transaction

Deadlock In SQL
Deadlock is one step ahead from Blocking. In deadlock two or more transaction have a  resource locked. Means in other words you can say when two or more then two process have a locked resources then deadlock occurs.
In deadlock each process requesting for a resource which is already locked by another process. Until the resource not released by the process the other process will not locked or use that resource.
You may also interested Learn Lock In SQL
You can understand this by given below image.

Image Source -http://csharp-video-tutorials.blogspot.in

Sql Server understand the deadlock condition, so when deadlock happens sql server automatically assumes one of the process as a victim and rollback the victim process and after this rollback the another process can executed
And when this deadlock happens that time the victim process gets the message similar to this.
Msg 1205, Level 13, State 51, Line 1

Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 

Best example for the deadlock practice in sql given below.
Open 2 instances of SQL server same you did with blocking. Now paste the below code on first SQL instance.

-- This is Process 1 Begin Tran UPDATE EmpDetail SET Address = 'Eng' WHere ID= 1 -- Now From Process 2 Window execute the first Begin statement -- Do not execute this before Process2 execution UPDATE EmpDetail1 SET Address = 'Eng' WHere ID= 1 -- From Transaction 2 window execute the second update statement Commit Transaction

Now on second instance of sql server paste the below given code

-- This is Process 2 Begin Tran UPDATE EmpDetail1 SET Address = 'Eng' WHere ID= 1 -- On Process 1 Window execte the second Update statment. UPDATE EmpDetail SET Address = 'Eng' WHere ID= 1 -- SQL assumes this instance as Victim and rollback the Process2 Commit Transaction

Change the tables and conditions base on your need. After doing this first run the EmpDetail table transaction on  Process 1 window. Then executes the EmpDetail1 table transaction on Process2 window.
After this executes the same table Update in Process1 window and then execute the EmpDetail update in Process2 Window. And when you hit on process2 last statement the deadlock occur. 
Because before start the execution both tables have no lock but when execution start EmpDetail is locked by Process1 and EmpDetail1 is locked by Process2. After this when Process1 wants to exute the EmpDetail1 which is laready locked by Process2 will not excuted but when you run the EmpDetail table query on Process2 window the deadlock occurs and 1 resource passed and other one rollback by SQL.

Read More

Lock A SQL Table Manually / Forcefully

Introduction
In this article we will learn how to lock a sql table and why do we need to lock a table , in which scenario we need to lock a table. What happens when any sql table locked.

Previous Updates
In previous articles we have learnt what is connection pooling in c# . How high quality content affects your Website. Why every business needed digital Marketing and Why digital marketing Required. Authorization in Asp.Net.

What is Lock on a table
When you working with database then it may be possible you are working with looping and your procedure executes your tables in loop and many times it also possible that the procedure plunge your table into a lock condition. Once your table locked you will not able to perform any operation on that table until the lock not released.
Means when any table Locks it means you are no longer to work with that table like DML operation (Insert, Update, Delete operations). You need to release the table from Lock after that it will work like a normal table.

Types of Lock in Sql server 
Many types of locks in sql server that can occur while performing operation in table such  as exclusive locks, shared locks, DML locks, transaction locks and backup-recovery locks.
              But there are a number of problems that can be caused by database locking. They can generally be broken down into 4 categories: Lock Contention, Long Term Blocking, Database Deadlocks, and System Deadlocks. 

Why do we need to lock a table
If you are randomly reading this topic then now may be think why do i need to lock a table if it will be bad for us. As per your point of view  Yes we don't need this . But many times when we need to test the table and restrict the operation performed on table. 
In my case i have faced the lock condition on table and i released the lock and then it works fine but  i have to reproduce the same scenario for testing purpose that's why i think that locking a table is also a good practice many times.

In real time you will not lock the table but you can delay the table using WAITFOR DELAY sql function. By giving time of delay you will restrict the table for the given time period.

BEGIN TRAN  SELECT 1 FROM YourTable_Name WITH (TABLOCKX) WAITFOR DELAY '00:0:10' ROLLBACK TRAN   GO -- Delay for 10 Seconds

Topics you may also interested  Cursor and Triggers in SQL. Difference Between SCOPE_IDENTITY(), @@IDENTITY and IDENT_CURRENT.

Now no one can perform operation on this table until the time delay will no elapsed. Here i execute this delay query by given time for 10 seconds and run the select statement for the same table in sql window but it will not return table data until the delay time not elapsed.. You can better understand by below given output image.

Read more about the Lock.

Read More

What Is Connection Pooling In Asp.Net Using C# With Example

Introduction
In this article we will learn what is connection pooling. Why connection pooling useful , what is the max limit and minimum limit of Connection pooling. Important key points about connection pooling.

Previous Updates
In previous articles we have learnt Showing Bind multiple dropdown list using single method call using c#. Read Excel file in C#  And Display in Grid. Read Excel in Windows Application project. Chart With Database In Asp.Net Using C#.  Insert Only Numeric Values In Asp TextBox Using Regex . Get TextBox , Dropdown, CheckBox  control Values In Aspx.cs Page From User Control Using C#.

What is Connection Pooling -
When we use ADO.Net in c# application then first thing we made connection object and open it. Create command object , data reader, data set, ExecuteNonQuery, ExecuteReader  and all that.
If open a connection many things happens before that connection made. It will be a long process and take some time.
              In our applications mostly we use one or two connection configurations and repeatedly same connection configuration will be opened and closed, automatically huge time will be consumed to open and close same database connection.
To reduce the cost of opening and closing the same connection repeatedly, ADO.NET uses an optimization technique called connection pooling.

Connection Pooling means once the connection object is opened then rather then going and recreating the connection again and again Ado.Net stores or cache the connection object in Pooler.
          Later if somebody going to opens the connection then it will not go the series of steps it already did , now it simply pic the connection from connection pool where it cached   already.

Once we finished operations on database we need to Close the connection then that connection will be returned to the pool and its ready to be reused on the next Open call.

If the connectionstring uses its slightest value even though you are connecting with same database or same localhost a new connection pool will be created . Means every unique connectionString a pool will be created.

  
Create Connection Pooling In Asp.Net C#
By default connection pooling is opened in Ado.Net , we did not to do anything for this. But we can disable the connection pooling manually by using  pooling = false in connection string.

Connection pooling Practice With C#

using (SqlConnection con = new SqlConnection("Data Source=GurujiPoint;                Initial Catalog=TestDB;Integrated Security=True; Pooling=false")) {     con.Open();     // Connection Pool will not created. we have set the pooling = false. } using (SqlConnection con = new SqlConnection("Data Source=GurujiPoint;                              Initial Catalog=TestDB;Integrated Security=True")) {     con.Open();     // Connection pool B will create. } using (SqlConnection con = new SqlConnection("Data Source=GurujiPoint;                            Initial Catalog=TestDB_1;Integrated Security=True")) {     con.Open();     //New pool C will created because connection string different.      //Both B and C database is different. } using (SqlConnection con = new SqlConnection("Data Source=GurujiPoint;                          Initial Catalog=TestDB_1;Integrated Security=True")) {     con.Open();     // No new Connectin Pool will created.      //Cause This connection string matches with Connection Pool C. }

Key Points

• The connections pooler will remove connections from the pool after it has been idle for approximately 4-8 minutes
• We can define maximum number of connections can be created in the pool. By default the maximum number of connection is 100 and we can add property like Max Pool Size=100.
• We can define minimum number of connections maintained in the pool. The default is 0. We can add property like Min Pool Size=0.
• Pooling allow us to set condition to add connection string to pool or not. By default its true. In case we don’t want to add it into pool then we need to define property like Pooling=false.

Read More