Sql Server Interview Questions: Store Procedures

A Store Procedure is a Sql Query that is stores in database in Sql Server. One thing is that there is no need to query to be write on front end code. Using store procedure improves performance now the question arise how its improve performance. Lets suppose we have to put some select query (select * from dotnetquestion where id>1000 and id<5000)

Here dotnetquestion is table and 1000 user are using this query at the same time. So lot of network traffic is there and network congestion occurs. Some of connection are disconnected due to network traffic and again reset and network traffic sent twice. So query really slow down the process.

Thus, in order to obtain exemption from the traffic on the network to some small amount of information transmitted over the network. So all these things done if stored information about SQL Server. Store Procedure saving role to play there, we need methods to save only a small command to execute procedures to deal with. Another advantage of the method is an overhead shop means that if we have some ad-hoc queries must be run every time especially if they are executed, but save precompiled procedures are created. When running store procedures in the collection plan is already in memory as compared to SQL query plan is based on the created shortcuts, and WHERE clauses. Thus, methods to save fasting.

DBMS Another big advantage when it is, we just each query, the server will be changed only need to change the query in one place.

But there are cases when the store process is useful when the question changed frequently. There are different types of storage process some of this stored procedure is as folm.

1. Define procedure Yuzasutoa
2. Systems Store Procedure
3. Extended Store Procedure

To learn more about Dot Net dotnetquestion.info Interview Questions visit