Stored procedure name tagging

Question

Ever wonder what wikipedia's database schema looks like? I recently read this thread from reddit.

I like how their tables are tagged with a prefix so you can sort of tell its functionality, purpose, and relationship with other tables right off the bat.

One thing I do not notice is how they name their stored procedures. Do they even use SP?

I use MS SQL Server. Prefixing all stored procedures with USP_ or SP_ seems redundant and anti-beneficial as the object explorer already sorts it all out for me. How do you name your SPs?

Answer 1

I personally will prefix my stored procedures with a unique name the describes what it does. For example.

SelectUserAccountById

or

InsertUserAccount

Typically the table name is referenced in the name, in the example above, the table would be UserAccount.

I do not prefix my stored procedures with SP or anything similar UNLESS I am building an extension that goes into a framework such as DotNetNuke, then I use a prefix for my company name.

Answer 2

I like how their tables are tagged with a prefix so you can sort of tell its functionality, purpose, and relationship with other tables right off the bat

That is why you have Schemas in SQL Server, you create a schema to group several object together and then you can give the HR person just access to the HR schema

Prefixing all stored procedures with USP_ or SP_ seems redundant and anti-beneficial as the object explorer already sorts it all out for me. How do you name your SPs?

SP_ should never be use because you will get a performance hit, whenever SQL server 'sees' a proc that starts with sp_ it will check the master database first, worst if MS decided to ship a proc with the sane name as yours and it starts with sp_ yours will never get executed

BTW not everyone is using the project explorer, some people like to do this in T-SQL

Answer 3

I find it useful to name the strored proc as TableName_Action

example RefClient_Insert, RefClient_Search, RefEmployee_Delete

This way, since the tables are grouped (Ref = Reference in this case) the SPs are grouped too.

Note that I have used _ just for clarity, you may skip it if you like.

Answer 4

I started naming all of the SQL objects with a widget type prefix. For example...

Photo Gallery Database Objects (abbreviated list)
Old Name          | New Name
-------------------------------------------------
tblCategories     | tblPGCategories
tblItems          | tblPGItems
spGetCategories   | spPGGetCategories
spUpdateCategory  | spPGUpdateCategory
spGetItems        | spPGGetItems
spUpdateItem      | spPGUpdateItems

Event Calendar Database Objects (abbreviated list)
Old Name          | New Name
-------------------------------------------------
tblCategories     | tblECCategories
tblItems          | tblECItems
spGetCategories   | spECGetCategories
spUpdateCategory  | spECUpdateCategory
spGetItems        | spECGetItems
spUpdateItem      | spECUpdateItems

We developed lots of websites and when a customer wanted a piece of functionalty we thought we could sell to others we would create it as a widget. We would then market these widgets to other customers.

This worked great until we started adding widgets we developed from other websites. We wound up with duplicate names for some of our code. So, out of necessity we implement a widget type naming convention. This made it very easy to integrate all the widgets we created.