Coding Standards - Part 1

  • Views tend to be across tables, so a prefix doesn't matter. Personally, I like having the names of the objects associated with the table, next to the table in an object list. Easier for me to find them.

    I don't often change from a non-clustered to a clustered index. Having IDX means I can easily find the clustered index in a group, and it isn't usually the PK.

    Steve Jones

  • Thanks for covering an often overlooked subject (by developers without team leaders anyway).

    The choice of convention is, well, a matter of choice. The main objective being consistency of names.

    I do have one suggestion to add regarding naming of stored procedures:

    I like the object.method convention used by c/VB/other programmers. I try to follow this convention since the SPs also form part of an application's code. The names are a bit subjective, but I think it makes things quite clear. Here are some examples:

    Company_Add, Company_Delete, Customer_Get, Customer_GetOrders, etc. When to use CustomerOrder_Get or Customer_GetOrders is the common ambiguity encountered. Sometimes the parameter(s) passed in will determine the best name, e.g. CustomerOrder_Get @Customer, @OrderNum vs. Customer_GetOrder @Customer. If anyone has any thoughts on this conflict, I'd really like to hear them.

    This convention also groups the procedures for any given table/entity together, i.e. all Company related functions are listed together in Enterprise Manager/Access/Query Analyser.

    I'm not a huge fan of the "sp_" prefix practice, becasue it means I can't just hit "C" on the keyboard to go to procs called "Company_...". Also I don't see that the prefix adds any real value/clarity.

    Thanks again for raising the topic.

  • Not a bad idea. One I hadn't thought of, especially since most databases only have a single target app. Might be easier to find them that way, though they'd be mixed with my Customer table, the CustomerAddress, CustomerType, etc.

    Steve Jones

  • [Interesting..

    But missing standards for procedures, which also has equal or more importance.

    Madhu V Pillai


    Comments posted to this topic are about the content posted at


  • Tend to agree about the proc names - thats the same reason I DONT use hungarian for property/method names in objects.


  • I like things grouped and since procs are usually some sort of function, I use sp (no underscore) to group them initially, then I go by function (Ins, Upd, Del, Add, Sum, etc).

    Steve Jones

  • In addition to the naming standards does anyone use or attempt to enforce descriptions of the objects in the meta-data? For example, when creating a database diagram, viewing properties of a table allows you to enter a description of the table; e.g. table CompanyRelationshipJoin "Creates a many-to-many relationship for the Company table for companies that do business with each other." When attempting to put together a data dictionary about the database objects, if there is a good "standard" approach anyone can recommend I would appreciate it. Also, it appears the descriptions you enter in the database diagrams don't show up in the Meta Data Services section so any suggestions about the best way to store the meta data would also be appreciated.

  • Interesting.. I have a few general comments on object naming. I use a very similar naming convention for my backup files, which helps tremendously when faced with restoring multiple files. Since I've written a stored procedure to handle all my backups, I write these file names to a table along with some other information, so I can automate scripting of my restores. That's off topic for here tho' sorry =)

    My other comment is, I don't indicate the object type in the object name. I don't see any need for this. I wouldn't prefix all my tables with tbl_XXXX and I wouldn't prefix my databases as db_XXXX, so why would I put trg_ on triggers? This isn't what naming conventions are all about.. the table_function idea is the way to go, eg book_insert, book_delete, book_list, book_getbybook_id etc.

    Also, for column naming a developer I work with has showed me the wisdom of proper column naming. I used to just use the standard like publication_date and drop_date etc, or first_name and last_name.. Now I use name_last, name_first and date_publication, date_drop as they are much easier to identify and group in a list of columns... My trigger naming convention isn't ironed out yet, I think it would be somewhat beneficial to indicate the table and column affected if possible in the trigger name. For example, on a table named journal_board_title a trigger that updates a last modified date field would be named upd_journal_board_title__last_modified. A bit lengthy (and I hate typing underscores with a passion), but helpful if you have multiple triggers firing for the same event.


  • Steve, when is Part 2 coming out?

  • Not sure. I've started it, but can't find time to finish it. I hope to have it by end of June.

    Steve Jones

  • I thoroughly enjoyed reading about other coding standards - unfortunately, many developers attach little or no importance to naming conventions, formatting etc..

    I, on the other hand, am almost obsessive about it - here are some of the standards that I have set for my development environment:

    1) I have a header template with all the comments that looks somewhat like this:


    Name: UP_WS_Enlisted_ClothingNumbers_INSERT

    Function: To take strength numbers from Clothing_T and insert them into Clothing_WorkSheet_T table.

    Details: details provided here

    Inputs: Int @E_FK

    Outputs: Int @ErrNum

    History: UserID 21

    Created Initial on 6/25/2002


    2) In my naming conventions I am not hesitant about using long & meaningful names that can tell me (or anyone else) at a glance what the procedure does.

    3) I use 'tab' a lot for readability. When I declare a variable, this is how it looks:

    DECLARE @Male_Rate Int

    DECLARE @Female_Rate Int

    4) My T-SQL statements look like this:

    UPDATE WorkSheet_Clothing_T

    SET Majority_Rate = @Male_Rate,

    Minority_Rate = @Female_Rate

    WHERE E_FK = @E_FK

    I use upper case for all the key SQL commands for further readability.

    5) All my tables use _T suffix; the views use _V suffix and so on...

    Thank you for these very useful & informative articles.(in the preview the tab spacing does not seem to work - so it does not look the way I have it in my code but it serves the purpose of explaining how it should look!)

    **ASCII stupid question, get a stupid ANSI !!!**

  • Glad you liked it and thanks everyone for the comments.

    I've got part 2 out now and have a part 3, which addresses some header comments in the next part.

    Steve Jones

  • Andy - About disallowing spaces completely - Excellent idea! For that matter, MS Access should be the same way!


    JASH - Just Another SQL Hacker

    Not bad! We don't have one much of one here. While senior people may rebel a bit, junior developers LIKE having a standard to follow. Not only that, they tend to survive by cloning code frequently, so if you have a a bunch of different styles in use already, it just gets worse!

    One thing I'd add, no spaces in database names either. If MS would PLEASE add a server configuration setting that will let disallow spaces in object names totally, that would SO useful.



  • I hate spaces in names. I understand how they work well for WORD documents, but not for databases or object names.

    Steve Jones

  • Very nice article, I always like these naming conventions... to be a standard in always...

    By the way one question, Why you used the trigger name(tr) and index names(IDX) at the end of the name ? Why can't we use it at the beginning ?

    if possible revert me at

    Thank u


    One ounce of practice is more important than tonnes of dreams

Viewing 15 posts - 16 through 30 (of 50 total)

You must be logged in to reply to this topic. Login to reply