Sql Server Interview Questions- Store Procedures by gjjur4356


									?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
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.

So to get release from network traffic sends some little information on network. So all
this things done when information is stored on Sql Server. Store procedure plays there
role to execute store procedure we need only a little command to handle store
procedure. Another advantage of store procedure is overhead means when we run
some ad hoc queries it must compiled mainly every time when they are run but store
procedure are precompiled. When store procedure gone in compiling plan is already
in memory as compares to Sql Query a plan is created on the basis of joins and where
clauses. So store procedures are fasters.

Another great advantage of DBMS is that when there is any change in query only we
have to change the query only at one place that is on the servers.

But there are also situations when store procedure is not helpful where query are
changed frequently. There are different types of store procedure some of these store
procedure are as folm.

1. user defined store procedure
2. Systems Store Procedure
3. Extended Store Procedure

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

Adam Sturo provides you the latest interview questions on Sql Server 2000 Interview
Questions and Xml Interview Questions.

To top