does it makes sense to do SP for simple queries like select * from users

is it going to be faster i开发者_如何学运维f instead of doing

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

I would create a SP for it ?

---

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.

---

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.

---

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.

---

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.