What is Merge Operation in SqlServer 2008?

Many Times while writing Sql Statement we use IF ELSE Statement ,IF EXISTS Statement & IF NOT EXISTS Statement and on the basis of found result we update ,Delete Or Insert the DataBase table.In that case we have to write number of lines.
In SqlServer 2008 Microsoft has provided MERGE statement which is not only simple but also have high performance.
The MERGE statement performs INSERT/UPDATE/DELETE operations on a target table based on the results of a join with a source table.
e.g. A School is conducting a Test for taking admission in new Batch and after Entrance Examination School Authority has defined some rule for rejection/selection of students .
Rules.
1. Delete All Records From The Student Table Where Marks < 30
2. Insert All the Records in The Student Table where Marks >=30 if not exists in the Student Table
3. Update the Result column of Student Table as Pass if Marks >30
Table ScreenShot
--Try to Implement the logic for the same as you are doing till now in sqlserver 2005 and than compare the effort and performance from below code using Merge Statement.
Using Merge Statement:

After Execution of the below Merge statement final output looks like as below.

Delegate to Lambda Expression.

What is delegate in C#?
Delegates are just like as a class that can be instantiated and work like a pointer which points to function.
Signature of a delegate is the Union of its return type and input parameter. So the function having same signature like delegate can be assigned to the reference of a delegate.
Syntax:
public delegate string Agent(int id, string name);
Here return type is string and input parameter is int & string.
So the signature is union of return Type and its Input Parameter.
Signature = string + (int, string).
So all the function having same signature like a delegate can be assigned to the reference of delegate.
First Approach for Creating Instance of delegate
Agent agnt;
Second Approach for Creating Instance of delegate
Agent agnt= new Agent();
IF we are having a function with same signature like delegate as below
public static string flatOnRent(int Id,string name)
{
string rtrnString="";
if (name == "Mulund")
{
rtrnString= "Flats Available For Rent in " + name.ToString();
}
return rtrnString;
}
And we want to assign this function to delegate than follow one of the below step.
Agent agnt= flatOnRent;
OR
Agent agnt= new Agent(flatOnRent);
Here we can see delegate agnt is now pointing to function flatOnRent.
Sample Code:

Decription of Code:
There was a agent who used to provide house on rent and for that he was having a fuction called ProcessRequest. After Some Time he thought to start selling of house beside providing the rent so he had modified function ProcessRequest() , but after few of months one of his friend suggest him to invest money in purchasing of flats but at this time Agent didnot want to modify the existing fuction ProcessRequest() , he was looking for a different approach where he could add more and more features in his business without altering existing function ProcessRequest(). he decided to use delegates where the requirement is supplied to delegate instead of passing it directly to ProcessRequest() method.

The Method ProcessRequest() is modified where it is accepting delegate(Agent) refernce
public static void processRequest(string[] strCity,Agent agnt)
{
foreach (string str in strCity){Console.WriteLine(agnt(str).ToString());}
}
Rest of the Steps are very simple
1. Declare the delegate
public delegate string Agent(string name);
2. Declare the fuctions with same signature like delegate
public static string flatOnRent(string name)
{
string rtrnString="";
if (name == "Mulund"){rtrnString= "Flats Available For Rent in " + name.ToString(); }
return rtrnString;
}
public static string buyFlat(string name)
{
string rtrnString="";
if (name == "Thane"){rtrnString="Flats Available in " + name.ToString() + " to Pucrchase";}
return rtrnString;
}

3. Now Call the ProcessRequest Method and pass the fuction which you want to assign the delegate refernce.
//1. Point delegate to flatOnRent method.
processRequest(strCity, flatOnRent);
//2. Point delegate to buyFlat method.
processRequest(strCity, buyFlat);

What is Anonoymous Method in C#?

Anonymous methods are just like as normal instance function without having a Name.It is little bit confusing because as we know fuctions are Named block of Code but Anonymous methods are opposite of that.So we can define the Anonoymous method as a shorthand for a delegate where we assign directly the body of function instead of declaring the function seperately.

we will modify our delegate code slightly in this time we will not declare the function flatOnRent and buyFlat seperately.We will directly assign the body of function to the refernce of delegate.

Syntax is very simple: delegate(inputParameter ip){body of the function;}

Sample Code:Description:

We can see from the above code that it becomes qiute compact as compare to delegate code just because of Anonymous method and result is same.

Note the below points

1. Methods flatOnRent() and buyFlat() are removed from the code

2. processRequest() is now accepting the anonymous method, basically it is still pointing to the delegate Agent thats why the delegate key word is used in the syntax.

Now the code
processRequest(strCity, flatOnRent);
processRequest(strCity, buyFlat);

is modified and it looks like as below

1. processRequest(strCity,delegate(string name){string rtrnString="";if(name=="Mulund"){rtrnString="Flats Available For Rent in "+name.ToString();}return rtrnString;});
2. processRequest(strCity,delegate(string name){string rtrnString="";if(name=="Thane"){rtrnString="Flats Available in "+name.ToString()+"to Pucrchase";}return rtrnString;});

What is Func<> function in C#?

Microsoft has created a generic delegate for us which is known as Func<>.It is overloaded delegate, we can create combination of signatures with this Func<> delegate.

Syntax:

Func<inputParameters,..........,Last Parameter is OutPutType> objFunc;

A Good news for us is that we neednot have to declare delegate seperately in our Code.

So our Previous Code became more compact.

Comapct Delegate Code:

What is Lambda Expressions in LINQ?

Lambda expressions are again shorthand for Anonymous Methods.Lambda expression are still pointing to delegate like Anonymous method.It could be more clear by below code statement.

In the previous code we have just eliminated the delegate key word and added a seperator i.e. => between the input parameter and body.very simple isn't it?

processRequest(strCity,(string name)=>{string rtrnString="";if(name=="Mulund"){rtrnString="Flats Available For Rent in "+name.ToString();}return rtrnString;});

Now at this stage you will be not shocked by seeing this odd syntax of Lambda because we have seen the evaluation of Lambda expression from the base.

we will continue to learn this Method Syntax of LINQ.

Modified Sample Code:

Alternate Sample Code with same output:

Final OutPut:

This output is same for all the code written from delegate to Lambda Expression

Extention Methods in C#?

Extention Methods are special type of Static methods which can be add to existing types without recompiling or modifing the original type.
Their first parameter specifies which type the method operates on, and the parameter is preceded by the this modifier.
Extension methods are only in scope when you explicitly import the namespace into your source code with a using directive.
Syntex:
Declare the class and its method as Static and the first parameter of function is preceeded by this key word.
Page 1.
namespace ExtensionMethods
{
public static class Utility
{

public static void Print(this object o)
{
Console.WriteLine("Called print: {0}", o.ToString());
}
public static int Increment(this int i)
{
return i++;
}
public static int Decrement(this int i)
{
return i--;
}
public static int Double(this int i)
{
return i * 2;
}
public static int Bonus(this int i)
{
return i + 5;
}
}
}
Page 2. Client Code
If we want to call the extention methods in our code than we need to first add the namespace of extention method.
namespace UseExtensionMethods
{
using ExtensionMethods;

static class Program
{
static void Main(string[] args)
{
int anotherNumber = 10;
int i = 7;
//e.g 1. Print(i);
i.Print();
//e.g 2.
//In the below line Increment method is directly called on variable anotherNumber.
anotherNumber.Increment();
//e.g 3.

//IN the below step first Method Increment is called and the method o/p is supplied to bonus method than decrement method is called and o/p is
//given to print method and finally result will be print on the screen.
anotherNumber.Increment().Bonus().Double().Decrement().Print();
Console.ReadLine();
}
}
}

What is Anonymous Type in C#?

O/P

Code description:

var runtimeClass=new {ID=1,Name="Bimlesh"};

In this above line we store the object of class in a variable of type var and create the class with new key word without having a hardcoded class name.

Like any other class we can access the properties of Anonymous class by its object .

runtimeClass.Id=1;

runtimeClass.Name="Bimlesh";

What is Table Valued Parameter in SqlSever 2008?

Sample Code From Database Front

Create Tabel Structure in Database

Database view

--Traditional Approach for Creating Insert SP
-- =============================================
-- Author: Bimlesh Singh
-- Create date: 05th June 2011
-- Description: Stored Procedure without Table Valued Parameter
-- =============================================
CREATE PROCEDURE usp_Insert
-- Add the parameters for the stored procedure here
@ID int ,
@Name nchar(10),
@Address_Line 1 nchar(10),
@Address_Line 2 nchar(10),
@Pin Code int
AS
BEGIN
-- Insert statements for procedure here
INSERT INTO Student VALUES (@ID,@Name,@Address_Line 1,@Address_Line 2,@Pin Code)
END
GO

--As per below screenshot we can see multiple times we call the same SP for Insertion

--TVP(Table Valued Parameter) Approach for Creating SP
-- =============================================
-- Author: Bimlesh Singh
-- Create date: 05th June 2011
-- Description: Stored Procedure without Table Valued Parameter
-- =============================================
CREATE PROCEDURE usp_Modified_Insert
-- Add the parameters for the stored procedure here
@StudentType Student Type
AS
BEGIN
-- Insert statements for procedure here
INSERT INTO Student SELECT * FROM @StudentType
END
GO

--From below screenshot we can see we need just the refernce of TVP for Insertion and i.e OneTime effort.

What is Caching?

Best SQL Practices

/*
**Please See Example at same Numbering Position below.
**1. Avoid selecting all fields.Select Only those which are required
**2. Avoid selecting filters in select statement
**5. Not operator Prevents From Indexing, so try to avoid it.
**6. Try to Avoid using 'Like' Operator always prefer 'In' and 'Between' Operator
**7. Functions and arithmetic applied against columns with the WHERE Clause means no chance of indexes
--. and repeated execution of the function or arithmetic.
**8. 'Union' returns distinct value where as 'Union all' returns all value including duplicate values.
**9. Avoid using complex Joins it decreases performance.
--The maximum number of tables that can be joined > 100, the practical limit is &lt; 10.
**
*/
--1.
SELECT * FROM dbo.TablName--Bad Practice
SELECT ID,Roll_No FROM dbo.TablName--Good Practice
--2.
SELECT ID,Roll_No FROM dbo.TablName WHERE ID=2--Bad Practice
SELECT Roll_No FROM dbo.TablName WHERE ID=2--Good Practice becuse It Decreases Overhead
--3.
SELECT DISTINCT Roll_No FROM dbo.TablName --If not Necessary don't use DISTINCT it added one more step for deleting duplicate records.
--4.
--If we are Comparing 2 column keep the dataType Size same
----Because it Increases DataType Conversion Overhead Unnecessary
--e.g EmpId(varchar 50)of a Table Compare with EmpId(varchar 20) of another Table
--5.
SELECT ID,Roll_No FROM dbo.TablName WHERE ID Not Between 1 and 3--Bad Practice
SELECT ID,Roll_No FROM dbo.TablName WHERE ID '>' 3--Good Practice
--6.
--If you want to search all Name like Bikesh,Bimlesh,Bikash i.e BIM to BIK
SELECT [Name] FROM dbo.TablName WHERE [Name] LIKE 'B%'--Bad Practice
--In the above statement it will check whole data
SELECT [Name] FROM dbo.TablName WHERE [Name] Between 'Bi'and 'Bj'
--In the above statement it will check only data starting from 'BI' to 'BJ'
--7.
SELECT Id FROM dbo.TablName WHERE ID + 1= 10--Bad Practice
SELECT Id FROM dbo.TablName WHERE ID = 10-1--Good Practice
--8.
--. Wanna to check if a Table is Blank or Not
SELECT count(*) FROM dbo.TablName WHERE ID=2--Bad Practice
SELECT top 1 [Name] FROM dbo.TablName WHERE ID=2--Good Practice
--9.
--Q.1 Find all the Name of Students whose Number lies between 1 to 33 if found Than say Fail
--Q.2 Find all the Name of Students whose Number lies between 34 to 55 if found Than say IInd division
--Q.3 Find all the Name of Students whose Number lies between 55 to 100 if found Than say Ist division

--BAD Approach:
SELECT [Name] FROM dbo.TablName WHERE Number Between 1 and 33
Print 'Fail'
SELECT [Name] FROM dbo.TablName WHERE Number Between 33 and 55
Print 'IInd Division'
SELECT [Name] FROM dbo.TablName WHERE Number Between 55 and 100
Print 'Ist Division'

--GOOD Approach:
SELECT [Name]
CASE WHEN Number BETWEEN 1 AND 33
THEN Print 'Fail'
CASE WHEN Number BETWEEN 33 AND 55
THEN Print 'IInd Division'
CASE WHEN Number BETWEEN 55 AND 100
THEN Print 'Ist Division' ELSE Print 'Fail' END,
FROM TablName;