Stored Procedures, Functions, and Views
Welcome to the 3rd and final installment on SQL naming conventions. In Part 1, we covered the rules for naming tables, while Part 2 explored conventions for column names. This installment will offer some guidelines for naming other database objects such as Stored Procedures, Functions, and Views.
Stored procedures
A stored procedure is a set of statement(s) that perform some defined actions. Typically, they contain statements that are used frequently. Stored procedures are similar to functions in programming in that they can accept parameters, and perform operations when we call them.
General Format
Most DBAs like to give their stored procedures a prefix that identifies it as such, followed by the action that the stored procedure takes and then the name representing the object or objects it will affect:
[prefix]_[action]_[object]
Actions that you may take with a stored procedure include:
- Insert
- Delete
- Update
- Select
- Get
- Validate
Choosing a Prefix
The most obvious prefix to use on a stored procedure is "sp_". That being said, there's at least one good reason to avoid it as it's already used by SQL Server as a standard naming convention in the master database. If you do not specify the database where the object is, SQL Server will first search the master database to see if the object exists there and then it will search the user database. Even if you don't host your database(s) on SQL Server, you should probably avoid using this as a naming convention, just in case you ever switch.
Instead, consider a prefix like "usp_" instead.
Putting It All Together
Here are a few examples of well-named stored procedures to formulate your own:
- usp_insert_person
- usp_delete_person
- usp_update_person
- usp_select_person
- usp_get_person
- usp_validate_person
User-defined Functions
Similar to built-in database functions, a user-defined function accepts only input parameters and contains a set of SQL statements that perform actions and return the result, which can be either a single value or a table.
The naming convention a for user-defined function is to have an "fn_" prefix, followed by its action. Hence, the syntax should be very similar to that of stored procedures:
[prefix]_[action]_[object]
Functions that return true or false may follow the rule of using "is" or "are" as the action (verb).
Some examples of function names would include:
- fn_count_string_instances
- fn_get_customer_balance
- fn_is_inventory_in_stock
- fn_get_column_type
Views
A view is a "virtual table" in a database that is defined by a query. A view can combine data from two or more table, using joins, and also just contain a subset of information. This makes them convenient to abstract, or hide, complicated queries.
The naming conventions for a view should have a "v_" or "vw_" prefix, followed by the namespace, results. As such, the syntax should be:
[prefix]_[result]
Here are a few examples:
- vw_actor_full_name
- vw_sales_by_store
- v_staff_list
- v_sales_by_product_category
Final Thoughts on Naming Conventions for Stored Procedures, Functions, and Views
In this three part series, we explored some commonly used naming conventions and considered how best to formulate our own. Part 1 covered Table names, while Part 2 focused on column names. Finally, Part 3 addressed Naming Conventions for other database objects such as Procedures, Functions, and Views.
Remember that you need not apply rules to all database objects. You could choose to apply naming convention rules to tables and column names only. It's really your decision, as using a naming convention is not mandatory, but beneficial nonetheless.
