By Joseph Sack
Discussions of naming conventions can, and usually do get ugly. There are so many ways you can do it. Some people don't give standards any thought, while other database developers are zealous to the point of renaming system generated constraints. How far should one go to implement standards across your databases? Do you apply standards to all database object types or just some (for example just views, tables, columns, stored procedures)? What about third party applications where you can't control or modify their object names and source code? The larger the team of database developers, the harder it is to build consensus. Even if you are able to pound out a standard, enforcing it can be difficult.
Why then, is it worth the effort? The best argument I've seen for establishing and using database object naming conventions is for ongoing supportability. Intuitively named database objects can be located more easily when troubleshooting a problem. Consistently named columns communicate their usage and "class" to the developer having to reference them. When it comes to the scope of your standard, I would recommend that any "home grown" code developed in your company should follow a standard. Third party products are outside of your scope, but that doesn't mean the rest of your code need be neglected.
Stored Procedure Naming Conventions
This article specifically discusses stored procedure naming standards. If your application uses stored procedures, defining a naming convention is absolutely critical for future supportability. If all of your Transact-SQL code is embedded in stored procedure, you'll need to know where to find exactly what you are looking for. This is particularly important for databases that contain procedures from multiple applications. You need a visual means of defining which procedures belong to what application, and have a general understanding of each procedures purpose.
First off, if I am absolutely positive that a single database will only be used for a single, less complex application, I’ll use the following naming conventions:
usp_ PredominantAction_TableNameOrBusinessProcess
The usp_ prefix is used to let me know that this is a user stored procedure. Some people don't like this prefix. I prefer it because I can easily identify stored procedure objects when querying system tables. I don’t want to be using sp_, as SQL Server interprets this as a system stored procedure.
The PredominantAction part describes what the stored procedure is intended to do. I’ve used an array of values over time, and I’m sure they will keep changing once I migrate to SQL Server 2005, but here is the list I’ve amassed so far:
SEL – Select – returns multiple rows
GET – Returns 1 row
UPD – Update
DEL – Delete
INS – Insert
EXT – Extract
IMP – Import
SAV – Combines insert/update
The TableNameOrBusinessProcess is used to tell me what the procedure references or does. If the procedure just works with a single table, I’ll use the table name. If it works with multiple tables to perform some kind of operation, I’ll use a brief process description.
Examples of this naming convention:
usp_SEL_SaleDetail
usp_UPD_Salesperson
usp_GET_ProcessIndicator
usp_IMP_MainframeFiles
If a database is shared across multiple applications or meaningful segments, I usually add a third element to the stored procedure name:
usp_NNN_ PredominantAction_TableName or Business Process
NNN indicates the project, application, or logical segment abbreviation. For example if you have a project called “Master Shipping Application”, you can use MSA as the code, for example:
usp_MSA_INS_ShipOrders
Or if you would like to segment procedures by areas in your application, you could designate the Sales site as SLS and Employee sit as EMP, for example:
usp_SLS_UPD_Budget
usp_EMP_SEL_AnnualReview
This standard allows for much improvisation based on your company standards and application requirements. No matter what standard you come up with, do at least choose to standardize stored procedures. I'm not talking about perfectionism here, just a general respect for how much time naming conventions will save you and your company down the road.
Discussions of naming conventions can, and usually do get ugly. There are so many ways you can do it. Some people don't give standards any thought, while other database developers are zealous to the point of renaming system generated constraints. How far should one go to implement standards across your databases? Do you apply standards to all database object types or just some (for example just views, tables, columns, stored procedures)? What about third party applications where you can't control or modify their object names and source code? The larger the team of database developers, the harder it is to build consensus. Even if you are able to pound out a standard, enforcing it can be difficult.
Why then, is it worth the effort? The best argument I've seen for establishing and using database object naming conventions is for ongoing supportability. Intuitively named database objects can be located more easily when troubleshooting a problem. Consistently named columns communicate their usage and "class" to the developer having to reference them. When it comes to the scope of your standard, I would recommend that any "home grown" code developed in your company should follow a standard. Third party products are outside of your scope, but that doesn't mean the rest of your code need be neglected.
Stored Procedure Naming Conventions
This article specifically discusses stored procedure naming standards. If your application uses stored procedures, defining a naming convention is absolutely critical for future supportability. If all of your Transact-SQL code is embedded in stored procedure, you'll need to know where to find exactly what you are looking for. This is particularly important for databases that contain procedures from multiple applications. You need a visual means of defining which procedures belong to what application, and have a general understanding of each procedures purpose.
First off, if I am absolutely positive that a single database will only be used for a single, less complex application, I’ll use the following naming conventions:
usp_ PredominantAction_TableNameOrBusinessProcess
The usp_ prefix is used to let me know that this is a user stored procedure. Some people don't like this prefix. I prefer it because I can easily identify stored procedure objects when querying system tables. I don’t want to be using sp_, as SQL Server interprets this as a system stored procedure.
The PredominantAction part describes what the stored procedure is intended to do. I’ve used an array of values over time, and I’m sure they will keep changing once I migrate to SQL Server 2005, but here is the list I’ve amassed so far:
SEL – Select – returns multiple rows
GET – Returns 1 row
UPD – Update
DEL – Delete
INS – Insert
EXT – Extract
IMP – Import
SAV – Combines insert/update
The TableNameOrBusinessProcess is used to tell me what the procedure references or does. If the procedure just works with a single table, I’ll use the table name. If it works with multiple tables to perform some kind of operation, I’ll use a brief process description.
Examples of this naming convention:
usp_SEL_SaleDetail
usp_UPD_Salesperson
usp_GET_ProcessIndicator
usp_IMP_MainframeFiles
If a database is shared across multiple applications or meaningful segments, I usually add a third element to the stored procedure name:
usp_NNN_ PredominantAction_TableName or Business Process
NNN indicates the project, application, or logical segment abbreviation. For example if you have a project called “Master Shipping Application”, you can use MSA as the code, for example:
usp_MSA_INS_ShipOrders
Or if you would like to segment procedures by areas in your application, you could designate the Sales site as SLS and Employee sit as EMP, for example:
usp_SLS_UPD_Budget
usp_EMP_SEL_AnnualReview
This standard allows for much improvisation based on your company standards and application requirements. No matter what standard you come up with, do at least choose to standardize stored procedures. I'm not talking about perfectionism here, just a general respect for how much time naming conventions will save you and your company down the road.
No comments:
Post a Comment