Stored Procedure:

what is Stored Procedure:

Stored Procedure in SQL Server can be defined as the set of logical group of SQL statements which are grouped to perform a specific task.

There are many benefits of using a stored procedure. The main benefit of using a stored procedure is that it increases the performance of the database.

Types of Stored Procedure.

1. User Defined Stored procedure:
 The user defined stored procedures are created by users and stored in the current database


2. System Stored Procedure
The system stored procedure have names prefixed with sp_. Its manage SQL Server through administrative tasks. Which databases store system stored procedures are master and msdb database

3. Temporary Stored Procedure:

The temporary stored procedures have names prefixed with the # symbol. Temporary stored procedures stored in the tempdb databases. These procedures are automatically dropped when the connection  terminates between client and server

4. Remote stored procedure:

The remote stored procedures are procedures that are created and stored in databases on remote servers. These remote procedures can be accessed from various servers, provided the users have the appropriate permission.

5. Extended  Stored procedure:

These are Dynamic-link libraries (DLL's) that are executed outside the SQL Server environment. They are identified by the prefix xp_

Benefits of Using the Stored Procedure

1. One of the main benefits of using the Stored procedure is that it reduces the amount of information sent to the database server. It can become a more important benefit when the bandwidth of the network is less. Since if we send the SQL query (statement) which is executing in a loop to the server through network and the network gets disconnected, then the execution of the SQL statement doesn't return the expected results, if the SQL query is not used between Transaction statement and rollback statement is not used.
2. Compilation step is required only once when the stored procedure is created. Then after it does not require recompilation before executing unless it is modified and reutilizes the same execution plan whereas the SQL statements need to be compiled every time whenever it is sent for execution even if we send the same SQL statement every time.
3. It helps in re usability of the SQL code because it can be used by multiple users and by multiple clients since we need to just call the stored procedure instead of writing the same SQL statement every time. It helps in reducing the development time.
4. Stored procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure instead of giving permission on the tables used in the Stored procedure.
5. Sometimes, it is useful to use the database for storing the business logic in the form of stored procedure since it makes it secure and if any change is needed in the business logic, then we may only need to make changes in the stored procedure and not in the files contained on the web server.

what is Difference between @@Identity,@scope Identity,Identity Current

@@Identity:

It returns the last identity value generated for any table in the current session, across all scopes.

Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then @@IDENTITY returns that identity record which is created by trigger.

@Scope Identity:

It returns the last identity value generated for any table in the current session and the current scope.

Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then SCOPE_IDENTITY result is not affected but if a trigger or a user defined function is affected on the same table that produced the value returns that identity record then SCOPE_IDENTITY returns that identity record which is created by trigger or a user defined function.

============================

INSERT Parent1 DEFAULT VALUES;

SELECT @@IDENTITY;

/*Returns the value 100. This was inserted by the trigger.*/

SELECT SCOPE_IDENTITY();

/* Returns the value 1. This was inserted by the

INSERT statement two statements before this query.*/ 

@IDENTITY CURRENT:

It returns the last identity value generated for a specific table in any session and any scope.

In other words, we can say it is not affected by scope and session, it only depends on a particular table and returns that table related identity value which is generated in any session or scope.

eXAMPLE:

SELECT IDENT_CURRENT('Child');

/* Returns the last value inserted into Child.*/

@@IDENTITY method is not limited to a specific scope.

SCOPE_IDENTITY method is limited for current scope.

IDENT_CURRENT method is not affected by scope and session, it only depends on a specific table.

Indexes

 What is Indexes in SQL.

 Indexes are used in relation database to retrieve the data to quickly.

Index is a pointer to data in a table.

Mainly an index increases the speed of data retrieval but it also increases the overhead of the data modification, like Insert, delete and update of the data. Now we consider some important points about indexes.


Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So, only create indexes on columns that will be frequently searched against.


Why do we need an Index in a table?

Suppose we have a table of records of employees that contain a million records and we want to search for a specific id of an employee. A normal search system will search for the employee id in each row. This process is called “Table Scan”. A table without a clustered-index is called a “heap table”. This process can require a large amount of time if the records are at the end of the table.


For overcoming this problem we can also use indexes. An index arranges the data of the table in a sorted manner in the form of a B-Tree (Hash Tables and R-Trees are also used).


Types of Indexes.

There are the following two main index types:

1. Clustered Index 

2. Non Clustered Index



Clustered Index:

1. In clustered index records in a table are sorted in physical order.
2. Therefor table have only one clustered index it is uses in primary key.
3. The leaf nodes of a Clustered Index contains the data pages. It is like a dictionary, 
4. when we create a table clustered index are automatically created.

Example:


Create Clustered Index My_ClusteredIndex  
on Employee_Detail(Emp_IId)  


2. Non Clustered Index:

A Non-Clustered Index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a Non-Clustered Index does not consist of the data pages. Instead, the leaf nodes contain index rows.


A Non-Clustered Index also uses a B-Tree. Each node of the B-Tree contains a key value and a pointer. The pointer holds the reference of the table row. This means that with a Non-Clustered Index there is extra work required to follow that pointer to the row in the table to retrieve any other desired values.



Query:
Create unique Nonclustered Index NonClusteredIndex  
on Employee_Detail(Emp_Name,Emp_Age)  


Imp:
Clustered Indexes are only the logical order of the index. In other words, a Clustered Index contains a key and the value of the row but a Non-Clustered Index only contains a key and a pointer to the row.



Difference B/w Clustered and Non-Clustered Index

1. A Clustered Index is a physical ordering of the data but a Non-Clustered Index is a logical ordering of the data (more like a pointer to the data).
2. A table has only one Clustered Index but a table can have one or more Non-Clustered Indexes.
3. Clustered Indexes are used when the data is unique but a Non-Clustered Index can work with clustered and Non-Clustered Indexes.
4. Data in a Clustered Index is always sorted but for Non-Clustered Indexes that is not necessary. 

Create Index:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Create Unique Index:

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

INSERT INTO SELECT

What is  INSERT INTO SELECT

The INSERT INTO SELECT statement copies data from one table and inserts it into another table.

INSERT INTO SELECT requires that data types in source and target tables match
The existing records in the target table are unaffected

Example:

INSERT INTO table2
SELECT * FROM table1

WHERE condition;

Select InTo

Select InTo
* it is used to copy data from one table to another.
* select data from one table and insert into new table.

Example:
Copy All column into new table.

SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

Union and Union All

1. What is Union.

It is used to combine result set of two or more select statements. In union select statement contain same no of columns and datatypes.

Union operator selects only distinct value by default. if you want to select duplicate value then used union all.

It remove duplicate records.

Example:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2

Union All

This operation again allows you to join multiple data sets into one data set, but it does not remove any duplicate rows. Because this does not remove duplicate rows this process is faster then Union.

Rules to union data:

Each query must have the same number of columns
Each column must have compatible data types
Column names for the final result set are taken from the first query
ORDER BY and COMPUTE clauses can only be issued for the overall result set and not within each individual result set
GROUP BY and HAVING clauses can only be issued for each individual result set and not for the overall result set

Different types of Sql Command

Different types of Sql Command:

1. DML (Data Manipulation language)
 *Select
 *Insert
 *Update
 *Delete

2. DDL (Data definition language)
It is used to create and modify the structure of database objects in database.

*Create
*Alter
*Drop
*Truncate

3.Data Control Language.  It is used to create roles,
 permissions, and referential integrity as well it is used to control access to database by securing it.

 *GRANT – Gives user’s access privileges to database
 *REVOKE – Withdraws user’s access privileges to database given with the GRANT command

4. TCL(Transaction control language)

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

COMMIT – Saves work done in transactions
ROLLBACK – Restores database to original state since the last COMMIT command in transactions
SAVE TRANSACTION – Sets a savepoint within a transaction