There are many advantages of Stored Procedures. I was once asked what do I think is the most important feature of Stored Procedure? I have to pick only ONE. It is tough question.
I answered : Execution Plan Retention and Reuse (SP are compiled and their execution plan is cached and used again to when the same SP is executed again)
Not to mentioned I received the second question following my answer : Why? Because all the other advantage known (they are mentioned below) of SP can be achieved without using SP. Though Execution Plan Retention and Reuse can only be achieved using Stored Procedure only.
- Execution plan retention and reuse
- Query auto-parameterization
- Encapsulation of business rules and policies
- Application modularization
- Sharing of application logic between applications
- Access to database objects that is both secure and uniform
- Consistent, safe data modification
- Network bandwidth conservation
- Support for automatic execution at system start-up
- Enhanced hardware and software capabilities
- Improved security
- Reduced development cost and increased reliability
- Centralized security, administration, and maintenance for common routines
Reference : Pinal Dave (https://blog.sqlauthority.com)
43 Comments. Leave new
Thanx Pinal Dave for contributions to ColdFusion(firstly) and (by now) SQL Server communities! But only a tip: for us, ColdFusion(and PHP, ASP, JSP, Perl, Ruby, and so on) developers, Stored Procedures pre-compilation doesn’t help because our dynamic applications will always sent different parameters, ya? Do you have tests about?
what is difference between sql server 2005 and sql server 2008
in sqlserver 2008 you have new datatypes like date,time,datetime and geografy .
in geografy you can sotore the videos
hi,
im in need of a sp to delete a particular table from the database and restoring the same table from a .dat file .. actualy its a concept of scheduling it has to be done everyday
looking for ur reply..
Thanks,
vivek
i want what is the diffrent between sql 2000 and 2005
any one able to telme
good question I will post u later
Yes, the difference between SQL 2000 and SQL 2005 is “5”.
Pinal Dave,
I have a system using Database “DBF.” Develop a program that makes the load of data updated in the “DBF” to SQL 2005, using the Advantage Database Server to make the reading of the data “DBF.” I have had some problems in reading this data. You could report some knowledge about this case?
Thanks!!!!
With the optimal usage of Prepared Statements even qury plan caching can be obtained so I feel the ability to tune and modular coding is the best advantage of stored procedures.
What are the disadvantages of stored procedure
except This?
stored procedure may hinder the performance
if it is excessively recompiled
Is it TRUE OR FALSE
execution plan is not cached in case of functions(udf)..?
hahaha….Great reply Lee…
Hi,
I have a query, how to select top two records from the table?
Waiting for your kind reply.
Thanks in advance.
Hi,
I have a query, how to select top two records from the table?
Waiting for your kind reply.
Thanks in advance.
for this u have to use top in the query i.e(“select top 2 field name from table _name)
for more info go to w3schools.com,,,,,
For this
you can try
select Top 2 Field Name from Table_Name
or
select Top 2 * from Table_Name
Thanks
Thanks Jaya Sharma.
Thanks to you sir for such a mind blowing and helpful post always.
@Vivek
Select top 2 * from Table_Name
~ IM.
@Imran
Good answer (as usual from you).
I’d like to suggest a line from the help file: “Parentheses that delimit expression in TOP is required in INSERT, UPDATE, and DELETE statements. For backward compatibility, TOP expression without parentheses in SELECT statements is supported, but we do not recommend this. ”
So, TOP(2).
Cool One…………
Could any one explain what is the difference between T-SQL and PL/SQL ?
SQL is the Structured Query Language the ANSI/ISO Standard database language. SQL Server’s implementation of the language is called Transact-SQL (T-SQL).
@madhav
PL/SQL is Oracle’s implementation of the SQL language.
T-SQL is Microsoft’s.
The differences are too many for a blog reply. I suggest you get some documentation on each if you need to know them both. They are, of course similar, as they adhere to the SQL standard.
Could any one explain what Query auto-parameterization is?
Hi Friends
Pls help me..,
I know stored procedure is used for reuse and fast execution.But my Question is How is do fast Execution? and How it is fast from inline query?
very nice article……..
pls solve my problem i want to know that how i can insert records in alter column or newly added column in sql server 2005
When you add new column to the existing table, you need to write update statement to update the values to that column
Hi mate,
Its 5 years since you blogged this post. Does these still make sense in the light of newer versions of SQL Servers?
Thanks,
Chris
If we pass the query to stored procedure and just write a simple exec statement to execute that query, do we still can get benefit of using stored procedure?
Example:
create procedure mySP
@qry varchar(max)
as
exec (@qry)
and @qry is passed through the application code like:
string qry = “select * from myTable”;
Thanks.