Difference between writing SQL query and stored procedure ?




Answers were Sorted based on User's Feedback



Difference between writing SQL query and stored procedure ?..

Answer / ashwini

SQL query will be compiled everytime it is executed.
Stored procedures are compiled only once when they are
executed for the first time.

Is This Answer Correct ?    206 Yes 18 No

Difference between writing SQL query and stored procedure ?..

Answer / gayathri

Every query is submited it will be compiled & then
executed.where as stored procedure is compiled
when it is submitted for the first time and this compiled
content is stored in something called procedure cache,
for subsequent calls no compilation,just execution & hence
better performance than query.
In Query we can execute only 1 statement. Stored Procedure:
In Stored Procedure we can execute Batch of Queries

Is This Answer Correct ?    102 Yes 11 No

Difference between writing SQL query and stored procedure ?..

Answer / senthil kumar

compared with query, stored procedure is efficient and fast
execution.Because stored procedure is program once it
formed then execute as a program.

Is This Answer Correct ?    90 Yes 26 No




Difference between writing SQL query and stored procedure ?..

Answer / vial

stored proceure compiles only once and stores in database
managemant system so it is optimized for best performance
while sql query compiles every time and executes using dbms
driver so its performance decreses

Is This Answer Correct ?    77 Yes 21 No

Difference between writing SQL query and stored procedure ?..

Answer / robinson

The execution plan will be created when compiling the stored
procedure and permanently stored in the database and so it
is much efficient, But in the case of query the execution
plan will be created at runtime.

Is This Answer Correct ?    30 Yes 9 No

Difference between writing SQL query and stored procedure ?..

Answer / raghuraja, sivaganga, india

Advantage in Programming - If you are using Query in your
program exe, want to change Query, you have to change query
and compile it again. But if used Stored procedure, no need
to recompile just modify the sp. Query is slow and SP is
fast.

Is This Answer Correct ?    30 Yes 10 No

Difference between writing SQL query and stored procedure ?..

Answer / prakash

1. SP is easy to allocate GRANT AND REVOKE (Privileges)
Rights for user

2. SP is execute more faster than query statement

3. SP, is already compiled Object

4. Query is Sql Statement where as SP batch of Sql Statement

5. In a query memory management is more high than the SP

Is This Answer Correct ?    27 Yes 8 No

Difference between writing SQL query and stored procedure ?..

Answer / kshitij s. nirmaley

query and stored procedure do the same thing but the
difference is that a query should be compiled every time the
query is executed, while the stored procedure is in compiled
form when executed first time .If we use stored procedure
we can avoid recompilation of the query.

Is This Answer Correct ?    23 Yes 5 No

Difference between writing SQL query and stored procedure ?..

Answer / pratap

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
CREATE PROCEDURE procedure_name
@param data_type = default_value,
@param data_type = default_value,
@param data_type = default_value
AS

BEGIN
DELETE FROM Employees
WHERE EmployeeId = @EmployeeId;
END

Is This Answer Correct ?    13 Yes 0 No

Difference between writing SQL query and stored procedure ?..

Answer / swathi

Queries are compiled each and evey time when we execute.
where as Stored Procedures are compiled only once when we
execute for the first time and the execution plans are
cached in memory.

Is This Answer Correct ?    9 Yes 3 No

Post New Answer



More SQL Server Interview Questions

Why we need a group by clause?

4 Answers  


Can you give an example of Stored Procedure?

4 Answers  


what is diffence between replicaion and logshipping?

1 Answers  


1 01 101 01010

0 Answers  


What is ACID Property of Transaction?

10 Answers  






How can your resolve deadlocks?

4 Answers   IBM, Iron Mountain, IMS, NuWare,


Can someone suggest me the best institute to learn SQL Server... I want to gain in depth knowledge on SQL Server Development. I have been to naresh, peers, sql school,mind q systems... Any idea about best faculty....

3 Answers  


what is a cursor and what is its use?

2 Answers   L&T,


I need a query that retrieves info from an Oracle table and a query that retrieves info from a SQL Server table. The info has to be joined together according to Record ID numbers. I have very limited access to the Oracle database but full control of the SQL Server database.How do I join two different queries from two different databases?

1 Answers  


How to Execute an Operating System Command From Within SQL Server ?

3 Answers  


difference between truncate, delete aur drop?

2 Answers  


i have a table student like sname ----- ram ram ram raj raj raj i need the output like sname ----- ram raj ram raj ram raj

9 Answers   IBM,






Categories