Define temporary and extended stored procedure.
Temporary Stored Procedure- Temporary Stored Procedure is stored in TempDB database.
- It is volatile and is deleted once connection gets terminated or server is restarted.
- Temporary stored procedures are prefixed with pound sign #.
- One pound sign means that it is temporary within the session.
- Two pound signs ## means it is a global temporary procedure.
- Global temporary procedure can be called by any connection to the SQL server during its lifetime.
Extended Stored Procedure- It is basically created to expand features of stored procedure.
- It uses external program and compiles as DLL. Mostly xp_ prefix is used as naming convention for extended stored procedure.
Define stored procedure.
Stored procedure is a set of SQL commands that have been complied and stored on the database sever. They can be used in the code as and when required since hey stored. They need not be complied over and over again. They can be invoked by CALL procedure (..) or EXECUTE procedure(..)
What are the purposes and advantages stored procedure?
Purposes and advantages of stored procedures:- Manage, control and validate data
- It can also be used for access mechanisms
- Large queries can be avoided
- Reduces network traffic since they need not be recompiled
- Even though the stored procedure itself may be a complex piece of code, we need not write it over and over again. Hence stored procedures increases reusability of code
- Permissions can be granted for stored procedures. Hence, increases security.
Determine when to use stored procedure to complete SQL Server tasks.
- If a large piece of code needs to be performed repeatedly, stored procedures are ideal
- When hundreds of lines of SQL code need to be sent; it is better to use stored procedure through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.
- When security is required.
Define temporary and extended stored procedure.
Extended stored procedure: Extended stored procedures allow a user to plug in custom functionality into SQL Server. They work with API’s and can be used to execute other Dlls.
Example: sp_MSgetversion: Used to get version of the SQL Server instance.
EXEC master..sp_MSgetversion
Temporary stored procedure: They are created with a prefix of # in their name. Temporary stored procedures are temporary within the current session. One might use them for applying small business rules which are not redundant across sessions but are redundant within a single session. They are dropped automatically when the user session ends.
