A hold on procedure could be a cluster of sql statements that has been created and hold on within the info. hold on procedure can settle for input parameters so one procedure is used over the network by many shoppers exploitation completely different computer file. hold on procedure can scale back network traffic and increase the performance. If we tend to modify hold on procedure all the shoppers can get the updated hold on procedure
Advantages of exploitation hold on procedures
a) hold on procedure permits standard programming.
You can produce the procedure once, store it within the info, and decision it any range of times in your program.
b) hold on Procedure permits quicker execution.
If the operation needs an oversized quantity of SQL code is performed repetitively, hold on procedures is quicker. square measure|they're} parsed and optimized after they are initial dead, and a compiled version of the hold on procedure remains in buffer store for later use. this suggests the hold on procedure doesn't ought to be reparsed and reoptimized with every use leading to abundant quicker execution times.
c) hold on Procedure will scale back network traffic.
An operation requiring many lines of Transact-SQL code is performed through one statement that executes the code during a procedure, instead of by causation many lines of code over the network.
d) hold on procedures give higher security to your knowledge
Users is granted permission to execute a hold on procedure notwithstanding they are doing not have permission to execute the procedure's statements directly.
In SQL we tend to area unit having differing types of hold on procedures area unit there
System hold on Procedures:
System hold on procedures area unit hold on within the master info and these area unit starts with a sp_ prefix. These procedures is accustomed perform form of tasks to support sql server functions for external application calls within the system tables
Ex: sp_helptext [StoredProcedure_Name]
User outlined hold on Procedures:
User outlined hold on procedures area unit sometimes hold on during a user info and area unit usually designed to complete the tasks within the user info. whereas secret writing these procedures don’t use sp_ prefix as a result of if we tend to use the sp_ prefix initial it'll check master info then it involves user outlined info
Extended hold on Procedures:
Extended hold on procedures area unit the procedures that decision functions from DLL files. currently a day’s extended hold on procedures area unit depreciated for that reason it might be higher to avoid exploitation of Extended hold on procedures.
CREATE PROCEDURE SPR_YourProcedureName
AS
--Write Your query here
GO
EXEC SPR_YourProcedureName;
GO
DROP PROCEDURE SPR_YourProcedureName;
GO
Advantages of exploitation hold on procedures
a) hold on procedure permits standard programming.
You can produce the procedure once, store it within the info, and decision it any range of times in your program.
b) hold on Procedure permits quicker execution.
If the operation needs an oversized quantity of SQL code is performed repetitively, hold on procedures is quicker. square measure|they're} parsed and optimized after they are initial dead, and a compiled version of the hold on procedure remains in buffer store for later use. this suggests the hold on procedure doesn't ought to be reparsed and reoptimized with every use leading to abundant quicker execution times.
c) hold on Procedure will scale back network traffic.
An operation requiring many lines of Transact-SQL code is performed through one statement that executes the code during a procedure, instead of by causation many lines of code over the network.
d) hold on procedures give higher security to your knowledge
Users is granted permission to execute a hold on procedure notwithstanding they are doing not have permission to execute the procedure's statements directly.
In SQL we tend to area unit having differing types of hold on procedures area unit there
a) System hold on Proceduresb) User outlined hold on proceduresc) Extended hold on Procedures
System hold on Procedures:
System hold on procedures area unit hold on within the master info and these area unit starts with a sp_ prefix. These procedures is accustomed perform form of tasks to support sql server functions for external application calls within the system tables
Ex: sp_helptext [StoredProcedure_Name]
User outlined hold on Procedures:
User outlined hold on procedures area unit sometimes hold on during a user info and area unit usually designed to complete the tasks within the user info. whereas secret writing these procedures don’t use sp_ prefix as a result of if we tend to use the sp_ prefix initial it'll check master info then it involves user outlined info
Extended hold on Procedures:
Extended hold on procedures area unit the procedures that decision functions from DLL files. currently a day’s extended hold on procedures area unit depreciated for that reason it might be higher to avoid exploitation of Extended hold on procedures.
0 comments:
Post a Comment