Take the 2-minute tour ×

is it going to be faster if instead of doing

select * from users where id = 1 
delete from users where id = 1
select count(*) from users

I would create a SP for it ?

share|improve this question
This is a possible duplicate of: stackoverflow.com/questions/2734007/…, which has some good answers. –  Paddy Jun 10 '10 at 14:05
Or rather, having followed a big duplicate chain, to this one: stackoverflow.com/questions/15142/… –  Paddy Jun 10 '10 at 14:07
add comment

4 Answers

up vote 3 down vote accepted

Performance-wise, no it doesn't make any difference.

Security-wise, it does made a difference. Using a sproc means you only need to grant execute permissions on the sproc, whereas the non-sproc approach would require permissions to be granted directly on the underlying table(s).

Network-traffic-wise - potential, slight/negligible difference. More applicable to larger statements whereby you either send the entire SQL statement across the wire or send just the sproc call. Pretty neglible overall.

Maintenance-wise - the sproc approach would allow you to (e.g.) tune a query without having to redeploy the whole application.

Something I'd be thinking is parameterising the query instead of using "hardcoded" values within an sql statement to support execution plan reuse.

share|improve this answer
add comment

If you're concerned about efficiency, don't do:

Select * From Users

Instead do:

Select column1, column2 From Users

Otherwise, SQL Server needs to do a lookup on all the columns in the Users table.

Personally, I wouldn't put something like this in a stored proc, but some people would, if they are doing all their data access via stored procedures.

share|improve this answer
add comment

If you are using SPs for all data access then yes. Otherwise I would not use it. It's never a good idea to have inconsistent behavior especially in source code.

I guess you are asking this question because you think that SPs are faster than inline queries sent by program but starting from sql server 2005 this is no more true as all execution plans are cached.

share|improve this answer
add comment

I'm leaning more towards a no on this one but then again I can see scenarios where you may want to do this.

For example, you may wish to implement a layer of security by utilizing stored procedures.

There is no improved plan reuse by using a stored procedure when considering the sample queries you have provided.

share|improve this answer
add comment

Your Answer


