- MS Sql Server
- 817
- March-20-2018
- by Rizwan Saqib
Store Procedures
Definition
A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name, which are stored in a relational database management system as a group, so it can be reused and shared by multiple programs.
Summary of Definition
Stored procedure are not pre-compiled, but compiled only during the first run. For every subsequent runs, it is for sure pre-compiled.
When we create a Stored procedure, first time and we will find that there are not casing entry for the executions of the Stored procedure.
After running the stored procedure for the first time, the entry for the cache is made in the system.
That's why Stored Procedure takes long time to run for first time.
Types Of Stored Procedures
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 ms db database
3.Temporary Stored procedures:
The temporary stored procedures have names prefixed with the # symbol. Temporary stored procedures stored in the temp db databases. These procedures are automatically dropped when the connection terminates between client and server
4.Remote Stored Procedures:
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 Procedures:
These are Dynamic-link libraries (DLL) that are executed outside the SQL Server environment. They are identified by the prefix xp
The benefits of using store procedures in SQL Server
- They allow modular programming.
- They allow faster execution.
- They can reduce network traffic.
- They can be used as a security mechanism.
