Joins in SQL server

In SQL joins are used to combine the data from two or more tables, it based on the relationship between columns in tables. Join is used to combine field from two tables by using values common to each.

The Example of Joins in given below:

Firstly we create two tables name emp and emp1

emp table – 

emp1 table -

Now we join these table in our select statement as following:

select id,name,dept from emp,emp1

where emp.Salary=emp1.salary

Output -  

Types of Joins:

Inner Join – return those rows who is match in both tables.

Left Join – It return all rows from left table ,if there is no row matches in the right table.

Right Join – it return all rows from right table, if there is no row matches in the left table.

Full Join – It returns rows when there is a match in one of the tables.

Self Join – Self join is used to join the table by itself.

Cartesian Join – it return Cartesian product of records from two or more joined tables.

                                                              Author - Sachin Pathak

Difference between the view State and session state in Asp.Net

View State	Session State
It is maintained in page level only	It is maintained in session level
View state of one page is not visible in another page	It is visible in all pages with in session
It’s information stored in client only	It’s information stored in server
It persist the value of particular page in the client browser when the post back operation is occur.	It persist the data of user in the server and data is available till the user close the browser.
It is used to persist page-instance-specific data.	It is used to persist the user-specific data.

What is the stored procedure?

Stored procedure are the group of SQL statement which run all the SQL statement in a single unit it is precompiled object if we call to stored procedure then there is no need to compile again.

Example of the stored procedure is given below:

Firstly we create a table with the name emp

Then create stored procedure:

create procedure abc(@id int, @nm varchar(20),@s1 int)

as

insert into emp values(@id, @nm, @s1)

return

After execute the above code we insert data into table by using the following syntax

exec abc 6,'Abi',22000

Output: 

 

Magic table in SQL Server

There are two types of Magic table in SQL Server:

Inserted
Deleted

Inserting into table (Inserted table):

When we insert anything in base table in database, a table is automatically created by the SQL server, it is called inserted, and this table contains current updated or inserted record. We can access the record of this table via triggers.

Updating Table (inserted or deleted table):

When we delete anything in base table in database, in spite of one, two tables are created one is called INSERTED and another is called DELETED. Deleted table contain current record after the deletion operation and Inserted table contain previous record .We can access via triggers.

Deleting (Deleted Table):


When we deleted anything in base table in Database, a table is created by SQL Server called deleted table, this table contain current updated record after deletion operation. We can access this record via triggers.

                                               Author : Sachin Pathak