Understand Stored Procedure in SQL Server

Introduction


Stored Procedure is a procedure which is pre-compiled SQL statement which accept input and return one or multiple value as an output.

This is a part of Procedural language which otherwise called TSQL.

If we are returning multiple value of different type then we should prefer "OUT" parameter.

If we are returning one value of type integer then we should prefer return type.


Lets Started

Lets take an example of simple stored procedure which return a result as an output.

create table test(id int primary key identity(1,1),name varchar(20),place varchar(20))

insert into test values('Raj','orissa')
insert into test values('Abi','AP')
insert into test values('Aku','MP')
insert into test values('Suresh','UP')
insert into test values('Baanu','Bihar')

CREATE PROCEDURE MYTestProc
(
    @id int
)
as
begin
   select * from test where id=@id

end

See the above stored-procedure  . We created by using the keyword "Create Procedure".You can run the procedure by pressing "F5". If want to modify after creation then we can use "Alter Procedure" keyword to modify.

I passed a parameter "@id" which can be used inside the procedure as shown in the Above code.We may pass or may not pass parameter to the stored procedure. 

"Stored procedure" Starts with "As Begin" and end with "End".
We can write our query inside of it.
It can be anything like Select,Delete,Update,Insert..etc.....

How can we execute the above procedure??

we can use "Exec" keyword to get the output.

EXEC mytestproc 1

Fig-1

See the little bit deeper and see how Output parameter works as below.

Lets Understand How to use out parameter

alter PROCEDURE MYTestProc
(
@id int,
@name varchar(20) output
)
as
begin
set @name=(select name from test where id=@id)
end

Executing the stored procedure using Out Parameter

declare @name varchar(20)

EXEC mytestproc 1,@name output

print @name

Output

Fig-2

Lets Understand How to use Return parameter

Create PROCEDURE MYTestProc

(

@name varchar(20)

)

as

begin

declare @id int

set @id=(select id from test where name=@name)

return @id                        //it will not execute the remaining statement if any
select * from test             //This statement will not execute

end

Executing the stored procedure using Return keyword

declare @id int

exec @id=MYTestProc 'Raj'

print @id

 Few things to remember about stored procedure

• We can use Create and Alter keyword while creating and modifying stored procedure respectively.

• To delete a procedure we can use DROP Procedure <Procedure_name>.

• Stored Procedure is a pre-compiled , so this is faster as compared to normal query.

• We can use Function inside a procedure.

• In return the remaining statement will not execute which comes after return.

• It is used as it used as a modular programming.

• It can be reusable so maintenance will be faster.

• It provide security.

Thanks...........

Share:

Read More

Denormalization in Sqlserver

Introduction

Before we understand denormalization Lets understand the table design . Table can be designed in 2 ways as below.

• Normalization
• Denormalization

see the below Fig(Fig-1).

Fig-1

Lets Get Started

Normalization is used to Disassociate the large table into a small tables(simpler format) having related data.It takes part in relational database.

Denormalization is just opposite to it. Smaller table converted to a larger table. So it does not take part in relational database.

There is an obvious question comes to our mind , if there is a process of making a table simpler with the related data then "why we need denormalization which again keep redundant data which is difficult to understand by the user?"

The question is really appreciated, but there is some scenario where we can use denormalization in stead of Normalization.

Lets understand why?

Lets take an example of a query which includes "Join" along with some manipulation.

Lets create three normalized tables Employee,Dept and Contact.I wanted some kind of manipulation.

Lets say joining. What happen when we try to manipulate huge numbers records(lac) and we know joining make the execution slower because of comparing from one table to another table. There is a chance of application timeout.

What is the solution then?

The Obvious choice is Denormalization.

see the below image.The left hand side called Normalized table where as right side table is called denormalized table(combination of 3 normalized table).

When retrieve complex calculation from multiple table and store into a single table which should show data with simple select statement which out any complex query like Joining or sub query or any other.

For  example in the above case we can write as below.

Select Empid,EmpName from <Denormalized Table> where <condition>

Few Points to remember about Denomalization

• It used to improve performance of the database.

• If the query is not much complex then we can prefer normalized table. If the query is complex then we follow the denormalized process.

• Always 1st preference is Normalized table . If that does not work and taking too much time to execute then we should go for De-normalization.

• De-Normalization contains redundant data unlike normalization.

Thanks.......

Share:

Read More

Run a Job in SQL Server

Introduction

When we think about a job in SQL Server then "SQL Server Agent" comes to our mind. Now one more question comes to our mind is Why we need a SQL Job in SQL Server?
Lets understand why exactly Job is?

Job is nothing but a continuous process which help to do some work based upon the schedule set by the user.
It simply automate the process as per the command.

In SQL Server we can create job using a tool called called Sql Server Agent which used to create job and do the automation based upon the command.

Get Started

Step - 1

Lets create a table as below.

create table tbl2(id int primary key identity(1,1),employeeName varchar(20),place varchar(40),addres varchar(20))

Step - 2

Suppose I wanted to insert a record to the database every 10 second with out user interaction.

How an i do this????

Yes, we can do this with the help of a job in sql server by using "Sql server Agent".

Lets understand how can we achieve this using Sql server agent?

See the below screenshot.

Fig-1

See the highlighted section. Where we can save our jobs . You can see "MyTestJob" which is created earlier.

Let create a new job . For that follow the below steps.

Go to "Sql Server Agent" --> New--> Job 

See the below screenshot.

Fig-2

Once you click on Job as shown in the figure then a popup window will open.

Fig-3

In general side menu give the details like Name of your job and its description as described above.

Then go to 2nd  option called Steps. Then Click New below the popup.

Then you can find a screen as below.

Fig-4

Fill the details as shown in the screenshot.

Then Return back as shownin Fig-3. Then Go to "Schedules" . And click New. Then fill the details as below.

Here The Date and time schedule for the job. This means in How many sec the job will trigger. 

Here we given 10 sec.

Lets click Ok. Again click OK to the window shown in Fig-3.

Lets start the SQL Server Agent if it has not started . To do that Right click on it and click Start.

Similarly to start the Job you can Right click the Job--> Click on "Start Job At Step".

Then You can see a record will insert to the table each 10 seconds.

Step - 3

Lets check whether our job is running or not..... 

Under "SQL Server Agent" we can find a tool called "Job Activity Monitor"  as below.

Right click it and click "View Job Activity".
Then a new popup will show which described about your job whether your job is "Enabled or Disabled" ,"Last job start time","Next schedule time" and much more.....

Conclusion

Basically Job is used to Automate the process which simplify the extra work and avoid to be error prone.

Thanks.............

Share:

Read More

Understand SQL Statement in SQL Server

Introduction

SQL-Structure Query Language which help to query,manipulate other operation in the database.SQL Server released in the year 1989 with version -SQL Server-1.0.Current version is Sql server-2017 released on 2017.

Lets Get Started

SQL Statements in sql server are broadly divided into 4 types. They are as below.

• DDL (Data Defination Language)
• DML (Data Manipulation Language)
• DCL (Data Control Language)
• TCS (Transaction Control Statement)

 Lets discuss below category wise.

 DDL-As name suggest it is used to define database structure. The database operation comes under this as below.

DDL Statement	Description
CREATE	Used to create the Database or table
ALTER	It used to modify the database structure.
DROP	Delete the table or Database
TRUNCATE	Used to delete all the records and associated table pace
RENAME	It rename the Database or Table

DML- As name suggest it used to manipulate data(Can not play with data structure) using the following statement.

DML Statement	Description
SELECT	It used to Select the records from single table,multiple table,function and view.
UPDATE	It used to update new data to the table.
INSERT	Used to insert data to the table
DELETE	Used to delete complete table or specific row from the table
LOCK TABLE	table used to lock one or more table in a specific mode.
MERGE	Used to merge two table data in a single pass

DCL- As name suggest it used to control the data by applying special permission to data.

DCL Statement	Description
GRANT	It give previlage to use the database resource.
REVOKE	It take back the permission given to the specific resource

TCS-As name suggest it helps us to provide the feature which help to permanent save as well as revert back the data.

TCS	Description
COMMIT	It used to commit the data when it get successful. After that you can not rollback.
ROLLBACK	Restore original data since the last commit takes place.
SAVEPOINT	Created for later use ROLLBACK the new changes.

We understood the types of statements available in sql server.
DDL,DML are most commonly used statement in Sql server.

Thanks....................

Share:

Read More

Introduction and Compiling Process in C#

Introduction

C#.Net is a high level language which is 1st introduced in 2002.The 1st version released is C#1.0 and is associated with the Framework 1.0.

The syntax of C# is almost equal to c++. So those who know C or C++ , its easy for them to learn syntactically.

See the below table which describe the Release details year wise.

c# version	Release Year	Framework
C# 1.0	2002	1.0
C# 1.1	2003	1.1
C# 1.2	2003	1.1
C# 2.0	2005	2
C# 3.0	2017	2,3,3.5
C# 4.0	2010	4
C# 5.0	2012	4.5
C# 6.0	2015	4.6
C# 7.0	2017	4.6.2
C# 7.1	2017	4.7
C# 7.2	2017	4.7.1
C# 7.3	2018	4.7.2

We can see the latest version is C# 7.3 released in 2018.

In c# lots of powerful feature are available . those are delegates,events,generics and many more.

Compiling process

using System;

namespace TestApplication

{

    class Program

    {

        public static void Main(string[] args)

        {

            Console.WriteLine("Test Class");

            Console.ReadKey();

        }

    }

}

See the above simple application.

when we try to run the above application Lets say console application, there are 2 things happening.

It compile the code and then run the code. In between these process we come across the below 2 compiler.

• C# Compiler
• JIT Compiler

Lets Understand by taking an example.

Fig-1

In the above image it convert C# language to MSIL code along with meta data. Then JIT Compiler converts MSIL to Machine language . 
We can use ILDASM to see the MSIL code in c#.

If compiling process are successful then we can see our output.



Thanks........................

Share:

Read More

Method Overridding in C#

Introduction

Before start "Method Overriding" lets understand Lets understand What is Polymorphism is.......

 Polymorphism is one of the most important pillar of OOPs in C#.  

Polymorphism is of 2 types.

• Compile time Polymorphism
• Run time Polymorphism

Lets Get Started....

See the below image(Fig-1).

Fig-1

Compile time polymorphism is called Method overloading where as Run time polymorphism is called Method Overriding.

Lets Get Started

As the name suggest it override the method. 

Lets take an example.

class ParentClass
    {
        public virtual void addition()
        {
            Console.WriteLine("parent");
        }
    }

    class childclass : ParentClass
    {
        public override void addition()
        {
            Console.WriteLine("child");
        }        
    }

class Program
    {
             public static void Main(string[] args)
        {
            childclass cp = new childclass();
            cp.addition();        //child method calld
            ParentClass pc = new ParentClass();
            pc.addition();        //parent method calld
            pc = cp;                //Reference variable of child class pointing to parent class object
            pc.addition();      //child method called
            Console.ReadKey();
        }
}

In the above code you can see We have 2 class. ''Parentclass''  and "ChildClass" . As we declare Virtual to parent class method and Override in child class method,  it can Override the Parentclass method.  when we try to access the method "addition()" then child method get called.

But if we want Parentclass method to be called then we can call the method by creating the object of the parent class.

What happen when the reference variable of child class pointing to the parent class object????It will call to the childclass method as shown in the above code. This is the specialty of method overridding.

Output

Method Overriding can be applied if  a method contains the keyword Virtual,abstract,Override.

In the above example we have seen how we used Virtual Keyword.Lets see an example where we can apply abstract and override to override the parent class.

abstract class absParentClass
    {
      public abstract void substraction();
        public virtual void addition()
        {
            Console.WriteLine("parent method called");
        }
    }
    class Childclass : absParentClass
    {
        public override void addition()
        {
            Console.WriteLine("child method called");
        }

        public override void substraction()
        {
            Console.WriteLine("abstract method called");
        }
    }
    class grandchildlass : Childclass
    {
        public override void addition()
        {
            Console.WriteLine("grand child addition");
        }
        public override void substraction()
        {
            Console.WriteLine("grand child");
        }
    }

See the above code. We can declare abstract keyword to the method in abstract class. The abstract method can be declared, can not defined. So we can define the method in child class by using the keyword "override" as shown above.

Now we understood the abstract keyword. But lets see an interesting thing related to Override....

Can we use "override" keyword to a parent class and override the same method in child class?

Of course. Lets see the above code.

Childclass and Grandchildclass having the methods and both use override keyword. Here in example Grandchildclass tried to access the childclass method using override keyword.

Here Just remember that we can not use static,virtual and new to modify the override keyword.

Few things need to be remember

• we can declare virtual,override,abstract keyword in parent class methods.
• We can only declare override in child class method.
• Object of child class pointing to the reference variable of parent class call the child class method.

Thanks.......................

Share:

Read More

Method Overloading in C#

Introduction

Before start "Method Overloading" lets understand the base concept behind this.

Method overloading is part of Polymorphism. Polymorphism is one of the most important pillar of OOPs in c#.  
Polymorphism is of 2 types as Fig-1.

• Compile time Polymorphism
• Run time Polymorphism

Lets Get Started

See the below image(Fig-1).

Fig-1

Compile time polymorphism is called Method overloading where as Run time polymorphism called Method Overriding.

Here we will discuss about Method Overloading.

Method overloading is nothing but the same method  name having different signature.

Lets understand the above point.

Lets take an example.

Fig-2

See the Fig-2 when we tried to add two method with the same name then c# compiler can not find out which method to call. That is the reason why we got compilation error. 

Now To solve this we can implement "Method Overloading" by applying different signature.

Lets see this in action.

see the below Code

  class MyPolymorphism

{

        public void Addition(int val1,int val2,int val3)

        {

        }

        public void Addition(int val1,int val2)

        {

        }

    }

    

We can see even though we have same method name we did not get any compile time error.This is called Method overloading.

Lets take another example.

What happen when we take same method name , same signature(same parameter) but different return type.

Lets see in action.

Fig-3

In Fig-3 we can see method define have different type (highlighted one) and The variable name are in different position. But still we get compilation error.

what happen when Method name are same, signature contains same parameter but the parameter interchange there position in two methods.

Lets see in Action.

class MyPolymorphism

    {

        public void Addition(int val1,string val2)

        {

           

        }

        public void Addition(string val1,int val2)

        {

        }

    }

See the above code.

We interchange the parameter where as method name is same.

Its working fine...............

see the below Image. Build succeed.

Fig-4

What happen when method name  and parameter type are same and are placed in same order. But we append out parameter to it.

Lets see in action.

Fig-5

See the above Image . It works as build succeed even though the parameter are in same order and method name are same. Only Parameter return type is different.

Conclusion

I hope concept of method overloading is cleared.

Remember the below points while implementing the method overloading.

• Method name should be same.
• Do not apply the same signature(same type in same order).
• Return type is not the part of  a method signature.
• You can apply method overloading by interchanging the method parameter having different type.

Share:

Read More

Boxing vs UnBoxing in CSharp

Introduction 

Before dive into the topic i.e. Boxing vs UnBoxing , Lets understand that datatypes are broadly divided into 2 types.

• Value Type
• Reference Type

Lets Get Started

Value type - : Value type is nothing but the type which stored in Stack.

Reference Type-: Reference type is nothing but the type which stored in Heap.

using System;

using System.Text;

namespace TestApplication{  

    class Program

    {

        public static void Main(string[] args)

        {

            int intval = 10;

            object objref = intval;//Boxing

            intval = (int)objref;//Unboxing

            Console.ReadKey();

        }         

    }

}




See the above code. In Boxing we are assign a value to an object Where in Unboxing we assign the object type to the integer.

In Boxing compiler will cast the type which is called Implicit conversion as below.
object objref = 10;

In UnBoxing compiler will not cast the type which is called Explicit conversion.
object objref = 10;
int intval = (int)objref;

See the below figure which demonstrate the boxing and unboxing. 

Fig-1

In Fig-1 demonstrated the data from Heap to Stack and Stack to heap which is in the other way we called Unboxing and Boxing respectively.

Things to remember

• Boxing and Unboxing degrade the Performance.
• Use Generic to avoid Boxing and UnBoxing.
• For Boxing Implicit conversion takes place.
• For UnBoxing Explicit conversion takes place.
• Try to avoid boxing and Unboxing as much as possible.

Thanks...............

Share:

Read More

Understand String vs String Builder in C-sharp

String Vs StringBuilder

Below are most important difference between "String" and "String Builder". 

Lets Discuss below.

Difference-1

"String Builder" is Mutable where as string is immutable. 

Mutable - You can change it.

Immutable - You cannot change it.

When we need to add more number of string to the variable that case we will use "String Builder" in stead of string. Lets understand Why?

See the below Code.

using System;
using System.Text;
namespace TestApplication{  
    class Program
    {
        
        public static void Main(string[] args)
        {
            StringBuilder sb = new StringBuilder();  //Using string builder
            sb.Append("Hi Raj");
            sb.Append("How Are You");
            sb.Append("?");

            string mystring="Hi Raj";                   //using string
            mystring = mystring + "How Are You";
            mystring = mystring + "?";

            Console.WriteLine(mystring + " -----" + sb.ToString());
            int i=mystring.CompareTo(sb.ToString());
            if (i == 0)
                Console.WriteLine("Both strings are same");
            else
                Console.WriteLine("Both strings different");  
            Console.ReadKey();
        }         
    }
}




Lets see the output.

Fig-1

See the Highlighted part---- both Strings are same.

The output is same for both. Then what is the Need of "String Builder" in this case?

Lets discuss the performance of string and "String builder".

Let see the below Images.

Fig-2

Fig-3

Fig-2 Represents String where as Fig-3 Represents String Builder.
For each string concatenation, "String" create a different memory location and store the Value as shown in Fig-2. For storing 3 values "string" create 3 memory location. That is the reason why it is called as Immutable.

In case of "String Builder" for each concatenation value will be replace in the same memory location as shown in Fig-3. That is the reason why it is called as mutable.

We can represent the string and string builder in a simple way as below.

Fig-4

See Fig-4 Multiple objects gets created for "String" when we add multiple strings but for "String Builder" only one object gets created.

Difference-2

String Builder use System.Text Namespace.
String Use System namespace.

Difference -3

String builder need to convert to string while displaying.
String no need to convert to any type.


Thanks................

Share:

Read More