Which method is best method for speed? in SQL Server, stored procedure

I have select, insert, update and delete query.

If I have to write all que开发者_如何学Pythonries in the same stored procedure that is good for performance or should I write all queries in separate stored procedures?

---

For ease of maintenance, I would go for separate procedures.

Speed is not going to be an issue if the same code is in one proc or multiple procs - as long as the code is the same, it will behave in the same way in one proc or many.

The best method to get good speed is to write an efficient query. Run it and review the execution plan; then tune the query where required.

You will find a lot of good information on query tuning and index tuning on this site (just search for it).

---

If it is something like, and all the parameters are manageable:

BEGIN TRANSACTION
    INSERT
    ....
    UPDATE
    ...
    DELETE
COMMIT

yes, all in one will eliminate the little overhead of multiple calls and keep the logic together as a unit.

however if it is:

@ParamType char(1) --given parameter "I"nsert, "U"pdate, "D"elete

IF @ParamType='I'
   INSERT
ELSE IF @ParamType='U'
   UPDATE
ELSE
    DELETE

split them up into separate procedures, they make no sense to be combined together.