What's the best practice of naming stored procedure for t-sql?

Question

I have worked with several big databases and the names of stored procedures were very different:

SP_PrefixXXX
PrefixYyyXxx
Prefix: Rep, Act

What's the best practice of naming? How can I organize them in a proper way?

Answer 1

The sp_ prefix stands for System Procedure, and it should not be used as prefix for regular procedures. If you do, it will first make an extra trip to the master database each time to look for the procedure, and if it would have the same name as one of the procedures there, that procedure will be executed instead of your procedure.

Other than that, you are free to make up any naming convention you like. One used by our company is subsystem_object_action, e.g. main_Customer_Get. That puts procedures that belong together close to each other in the listing.