Database Naming Conventions by Microsoft?











up vote
58
down vote

favorite
30












I found Naming Guidelines from MSDN, but is it any guideline for MSSQL database from Microsoft?










share|improve this question




















  • 2




    There are excellent answers below, but I would add the following: Agreeing on and following a convention within your org for the organization of your DB (including naming) are just as important. For example, we try to keep primary key columns first, followed by all foreign key columns so you can find relationships at a glance, followed by all additional columns in alphabetical order so you can find the one you want when a table has tons of columns. The wisdom of our specific conventions is debatable, but the value of having that conversation on your team probably is not.
    – Eric Burcham
    Oct 18 '13 at 15:34

















up vote
58
down vote

favorite
30












I found Naming Guidelines from MSDN, but is it any guideline for MSSQL database from Microsoft?










share|improve this question




















  • 2




    There are excellent answers below, but I would add the following: Agreeing on and following a convention within your org for the organization of your DB (including naming) are just as important. For example, we try to keep primary key columns first, followed by all foreign key columns so you can find relationships at a glance, followed by all additional columns in alphabetical order so you can find the one you want when a table has tons of columns. The wisdom of our specific conventions is debatable, but the value of having that conversation on your team probably is not.
    – Eric Burcham
    Oct 18 '13 at 15:34















up vote
58
down vote

favorite
30









up vote
58
down vote

favorite
30






30





I found Naming Guidelines from MSDN, but is it any guideline for MSSQL database from Microsoft?










share|improve this question















I found Naming Guidelines from MSDN, but is it any guideline for MSSQL database from Microsoft?







sql sql-server tsql






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Aug 29 '10 at 5:20









OMG Ponies

253k61439466




253k61439466










asked Aug 29 '10 at 5:00









Cheung

6,877175487




6,877175487








  • 2




    There are excellent answers below, but I would add the following: Agreeing on and following a convention within your org for the organization of your DB (including naming) are just as important. For example, we try to keep primary key columns first, followed by all foreign key columns so you can find relationships at a glance, followed by all additional columns in alphabetical order so you can find the one you want when a table has tons of columns. The wisdom of our specific conventions is debatable, but the value of having that conversation on your team probably is not.
    – Eric Burcham
    Oct 18 '13 at 15:34
















  • 2




    There are excellent answers below, but I would add the following: Agreeing on and following a convention within your org for the organization of your DB (including naming) are just as important. For example, we try to keep primary key columns first, followed by all foreign key columns so you can find relationships at a glance, followed by all additional columns in alphabetical order so you can find the one you want when a table has tons of columns. The wisdom of our specific conventions is debatable, but the value of having that conversation on your team probably is not.
    – Eric Burcham
    Oct 18 '13 at 15:34










2




2




There are excellent answers below, but I would add the following: Agreeing on and following a convention within your org for the organization of your DB (including naming) are just as important. For example, we try to keep primary key columns first, followed by all foreign key columns so you can find relationships at a glance, followed by all additional columns in alphabetical order so you can find the one you want when a table has tons of columns. The wisdom of our specific conventions is debatable, but the value of having that conversation on your team probably is not.
– Eric Burcham
Oct 18 '13 at 15:34






There are excellent answers below, but I would add the following: Agreeing on and following a convention within your org for the organization of your DB (including naming) are just as important. For example, we try to keep primary key columns first, followed by all foreign key columns so you can find relationships at a glance, followed by all additional columns in alphabetical order so you can find the one you want when a table has tons of columns. The wisdom of our specific conventions is debatable, but the value of having that conversation on your team probably is not.
– Eric Burcham
Oct 18 '13 at 15:34














3 Answers
3






active

oldest

votes

















up vote
119
down vote



accepted










The naming conventions used in SQL Server's AdventureWorks database demonstrate many best practices in terms of style.



To summarize:




  • Object names are easily understood

  • Table names are not pluralized
    ("User" table not "Users")

  • Abbreviations are few, but allowed
    (i.e. Qty, Amt, etc.)

  • PascalCase used exclusively with the
    exception of certain column names
    (i.e. rowguid)

  • No underscores

  • Certain keywords are allowed (i.e.
    Name)

  • Stored procedures are prefaced with
    "usp"

  • Functions are prefaced with "ufn"


You can find more details here:




  • AdventureWorks Data Dictionary

  • Stored Procedures in
    AdventureWorks

  • Functions in AdventureWorks


One caveat: database naming conventions can be very controversial and most database developers I've met have a personal stake in their style. I've heard heated arguments over whether a table should be named "OrderHeader" or "OrderHeaders."






share|improve this answer



















  • 3




    I love that you link to the original article, and I REALLY love that you bothered to summarize for everyone. Wish I could upvote twice.
    – Eric Burcham
    Oct 18 '13 at 15:31










  • @8kb what about Database names? Pluralized?
    – Jared Beach
    Jun 7 '16 at 21:08










  • Six years later and still getting +1 for a well laid out answer with links and good summary.
    – AgapwIesu
    Sep 16 '16 at 18:17


















up vote
12
down vote













No, there isn't but the practices in the link you provided are good to keep in mind.



With respect to naming stored procedures - do not prefix them with "sp_" You can read more about why in this link:




"Do not prefix stored procedures with
sp_, because this prefix is reserved
for identifying system-stored
procedures."







share|improve this answer



















  • 2




    I added the relevant quote from the article because it's short, and we can't expect a link to a 5-year-old post to last forever.
    – Gabe
    Aug 29 '10 at 5:24








  • 1




    sp_ is not reserved it just causes the SQL Server to search system procedures before searching user defined procedures.
    – user275683
    Nov 13 '13 at 21:11


















up vote
5
down vote













I don't know what "best practices in terms of style" in the answer by @8kb (at the time of writing) means. Certainly some of the listed items ("Table names are not pluralized", "No underscores", etc) are mere style choices which are obviously subjective. I would have thought the personal preferences of the documentation team lead would be the greatest factor here.



As regards heuristics in SQL in general (as opposed to proprietary SQL such as T-SQL), there is but one book on the subject: Joe Celko's SQL programming style.Many of the choices for SQL Server's AdventureWorks database conflict with Celko's guidelines.



Celko's naming convention is based on on the international standard ISO 11179 e.g. specifies that a delimiting character (such as an underscore) should be used to separate elements in a name. Other style choices are similarly backup up by research e.g. using exclusively lower case letters for column names so aid scanning by the human eye. No doubt there are subjective personal preferences in there too but they are based on many years of experiences out in the field.



On the plus side, things have improved in the SQL Server docs in recent years e.g. SQL keywords capitalized, semi-colons to separate statements, etc. Adventure works is a vast improvement on Northwind and pubs. Now why can't the scripting feature in Management Studio spit out code that is a little easier on the eye?!






share|improve this answer





















    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














     

    draft saved


    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f3593582%2fdatabase-naming-conventions-by-microsoft%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    3 Answers
    3






    active

    oldest

    votes








    3 Answers
    3






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    119
    down vote



    accepted










    The naming conventions used in SQL Server's AdventureWorks database demonstrate many best practices in terms of style.



    To summarize:




    • Object names are easily understood

    • Table names are not pluralized
      ("User" table not "Users")

    • Abbreviations are few, but allowed
      (i.e. Qty, Amt, etc.)

    • PascalCase used exclusively with the
      exception of certain column names
      (i.e. rowguid)

    • No underscores

    • Certain keywords are allowed (i.e.
      Name)

    • Stored procedures are prefaced with
      "usp"

    • Functions are prefaced with "ufn"


    You can find more details here:




    • AdventureWorks Data Dictionary

    • Stored Procedures in
      AdventureWorks

    • Functions in AdventureWorks


    One caveat: database naming conventions can be very controversial and most database developers I've met have a personal stake in their style. I've heard heated arguments over whether a table should be named "OrderHeader" or "OrderHeaders."






    share|improve this answer



















    • 3




      I love that you link to the original article, and I REALLY love that you bothered to summarize for everyone. Wish I could upvote twice.
      – Eric Burcham
      Oct 18 '13 at 15:31










    • @8kb what about Database names? Pluralized?
      – Jared Beach
      Jun 7 '16 at 21:08










    • Six years later and still getting +1 for a well laid out answer with links and good summary.
      – AgapwIesu
      Sep 16 '16 at 18:17















    up vote
    119
    down vote



    accepted










    The naming conventions used in SQL Server's AdventureWorks database demonstrate many best practices in terms of style.



    To summarize:




    • Object names are easily understood

    • Table names are not pluralized
      ("User" table not "Users")

    • Abbreviations are few, but allowed
      (i.e. Qty, Amt, etc.)

    • PascalCase used exclusively with the
      exception of certain column names
      (i.e. rowguid)

    • No underscores

    • Certain keywords are allowed (i.e.
      Name)

    • Stored procedures are prefaced with
      "usp"

    • Functions are prefaced with "ufn"


    You can find more details here:




    • AdventureWorks Data Dictionary

    • Stored Procedures in
      AdventureWorks

    • Functions in AdventureWorks


    One caveat: database naming conventions can be very controversial and most database developers I've met have a personal stake in their style. I've heard heated arguments over whether a table should be named "OrderHeader" or "OrderHeaders."






    share|improve this answer



















    • 3




      I love that you link to the original article, and I REALLY love that you bothered to summarize for everyone. Wish I could upvote twice.
      – Eric Burcham
      Oct 18 '13 at 15:31










    • @8kb what about Database names? Pluralized?
      – Jared Beach
      Jun 7 '16 at 21:08










    • Six years later and still getting +1 for a well laid out answer with links and good summary.
      – AgapwIesu
      Sep 16 '16 at 18:17













    up vote
    119
    down vote



    accepted







    up vote
    119
    down vote



    accepted






    The naming conventions used in SQL Server's AdventureWorks database demonstrate many best practices in terms of style.



    To summarize:




    • Object names are easily understood

    • Table names are not pluralized
      ("User" table not "Users")

    • Abbreviations are few, but allowed
      (i.e. Qty, Amt, etc.)

    • PascalCase used exclusively with the
      exception of certain column names
      (i.e. rowguid)

    • No underscores

    • Certain keywords are allowed (i.e.
      Name)

    • Stored procedures are prefaced with
      "usp"

    • Functions are prefaced with "ufn"


    You can find more details here:




    • AdventureWorks Data Dictionary

    • Stored Procedures in
      AdventureWorks

    • Functions in AdventureWorks


    One caveat: database naming conventions can be very controversial and most database developers I've met have a personal stake in their style. I've heard heated arguments over whether a table should be named "OrderHeader" or "OrderHeaders."






    share|improve this answer














    The naming conventions used in SQL Server's AdventureWorks database demonstrate many best practices in terms of style.



    To summarize:




    • Object names are easily understood

    • Table names are not pluralized
      ("User" table not "Users")

    • Abbreviations are few, but allowed
      (i.e. Qty, Amt, etc.)

    • PascalCase used exclusively with the
      exception of certain column names
      (i.e. rowguid)

    • No underscores

    • Certain keywords are allowed (i.e.
      Name)

    • Stored procedures are prefaced with
      "usp"

    • Functions are prefaced with "ufn"


    You can find more details here:




    • AdventureWorks Data Dictionary

    • Stored Procedures in
      AdventureWorks

    • Functions in AdventureWorks


    One caveat: database naming conventions can be very controversial and most database developers I've met have a personal stake in their style. I've heard heated arguments over whether a table should be named "OrderHeader" or "OrderHeaders."







    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Nov 10 at 21:21









    Vince Horst

    786824




    786824










    answered Aug 29 '10 at 6:39









    8kb

    8,18573149




    8,18573149








    • 3




      I love that you link to the original article, and I REALLY love that you bothered to summarize for everyone. Wish I could upvote twice.
      – Eric Burcham
      Oct 18 '13 at 15:31










    • @8kb what about Database names? Pluralized?
      – Jared Beach
      Jun 7 '16 at 21:08










    • Six years later and still getting +1 for a well laid out answer with links and good summary.
      – AgapwIesu
      Sep 16 '16 at 18:17














    • 3




      I love that you link to the original article, and I REALLY love that you bothered to summarize for everyone. Wish I could upvote twice.
      – Eric Burcham
      Oct 18 '13 at 15:31










    • @8kb what about Database names? Pluralized?
      – Jared Beach
      Jun 7 '16 at 21:08










    • Six years later and still getting +1 for a well laid out answer with links and good summary.
      – AgapwIesu
      Sep 16 '16 at 18:17








    3




    3




    I love that you link to the original article, and I REALLY love that you bothered to summarize for everyone. Wish I could upvote twice.
    – Eric Burcham
    Oct 18 '13 at 15:31




    I love that you link to the original article, and I REALLY love that you bothered to summarize for everyone. Wish I could upvote twice.
    – Eric Burcham
    Oct 18 '13 at 15:31












    @8kb what about Database names? Pluralized?
    – Jared Beach
    Jun 7 '16 at 21:08




    @8kb what about Database names? Pluralized?
    – Jared Beach
    Jun 7 '16 at 21:08












    Six years later and still getting +1 for a well laid out answer with links and good summary.
    – AgapwIesu
    Sep 16 '16 at 18:17




    Six years later and still getting +1 for a well laid out answer with links and good summary.
    – AgapwIesu
    Sep 16 '16 at 18:17












    up vote
    12
    down vote













    No, there isn't but the practices in the link you provided are good to keep in mind.



    With respect to naming stored procedures - do not prefix them with "sp_" You can read more about why in this link:




    "Do not prefix stored procedures with
    sp_, because this prefix is reserved
    for identifying system-stored
    procedures."







    share|improve this answer



















    • 2




      I added the relevant quote from the article because it's short, and we can't expect a link to a 5-year-old post to last forever.
      – Gabe
      Aug 29 '10 at 5:24








    • 1




      sp_ is not reserved it just causes the SQL Server to search system procedures before searching user defined procedures.
      – user275683
      Nov 13 '13 at 21:11















    up vote
    12
    down vote













    No, there isn't but the practices in the link you provided are good to keep in mind.



    With respect to naming stored procedures - do not prefix them with "sp_" You can read more about why in this link:




    "Do not prefix stored procedures with
    sp_, because this prefix is reserved
    for identifying system-stored
    procedures."







    share|improve this answer



















    • 2




      I added the relevant quote from the article because it's short, and we can't expect a link to a 5-year-old post to last forever.
      – Gabe
      Aug 29 '10 at 5:24








    • 1




      sp_ is not reserved it just causes the SQL Server to search system procedures before searching user defined procedures.
      – user275683
      Nov 13 '13 at 21:11













    up vote
    12
    down vote










    up vote
    12
    down vote









    No, there isn't but the practices in the link you provided are good to keep in mind.



    With respect to naming stored procedures - do not prefix them with "sp_" You can read more about why in this link:




    "Do not prefix stored procedures with
    sp_, because this prefix is reserved
    for identifying system-stored
    procedures."







    share|improve this answer














    No, there isn't but the practices in the link you provided are good to keep in mind.



    With respect to naming stored procedures - do not prefix them with "sp_" You can read more about why in this link:




    "Do not prefix stored procedures with
    sp_, because this prefix is reserved
    for identifying system-stored
    procedures."








    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Aug 29 '10 at 5:23









    Gabe

    70.4k8114203




    70.4k8114203










    answered Aug 29 '10 at 5:20









    OMG Ponies

    253k61439466




    253k61439466








    • 2




      I added the relevant quote from the article because it's short, and we can't expect a link to a 5-year-old post to last forever.
      – Gabe
      Aug 29 '10 at 5:24








    • 1




      sp_ is not reserved it just causes the SQL Server to search system procedures before searching user defined procedures.
      – user275683
      Nov 13 '13 at 21:11














    • 2




      I added the relevant quote from the article because it's short, and we can't expect a link to a 5-year-old post to last forever.
      – Gabe
      Aug 29 '10 at 5:24








    • 1




      sp_ is not reserved it just causes the SQL Server to search system procedures before searching user defined procedures.
      – user275683
      Nov 13 '13 at 21:11








    2




    2




    I added the relevant quote from the article because it's short, and we can't expect a link to a 5-year-old post to last forever.
    – Gabe
    Aug 29 '10 at 5:24






    I added the relevant quote from the article because it's short, and we can't expect a link to a 5-year-old post to last forever.
    – Gabe
    Aug 29 '10 at 5:24






    1




    1




    sp_ is not reserved it just causes the SQL Server to search system procedures before searching user defined procedures.
    – user275683
    Nov 13 '13 at 21:11




    sp_ is not reserved it just causes the SQL Server to search system procedures before searching user defined procedures.
    – user275683
    Nov 13 '13 at 21:11










    up vote
    5
    down vote













    I don't know what "best practices in terms of style" in the answer by @8kb (at the time of writing) means. Certainly some of the listed items ("Table names are not pluralized", "No underscores", etc) are mere style choices which are obviously subjective. I would have thought the personal preferences of the documentation team lead would be the greatest factor here.



    As regards heuristics in SQL in general (as opposed to proprietary SQL such as T-SQL), there is but one book on the subject: Joe Celko's SQL programming style.Many of the choices for SQL Server's AdventureWorks database conflict with Celko's guidelines.



    Celko's naming convention is based on on the international standard ISO 11179 e.g. specifies that a delimiting character (such as an underscore) should be used to separate elements in a name. Other style choices are similarly backup up by research e.g. using exclusively lower case letters for column names so aid scanning by the human eye. No doubt there are subjective personal preferences in there too but they are based on many years of experiences out in the field.



    On the plus side, things have improved in the SQL Server docs in recent years e.g. SQL keywords capitalized, semi-colons to separate statements, etc. Adventure works is a vast improvement on Northwind and pubs. Now why can't the scripting feature in Management Studio spit out code that is a little easier on the eye?!






    share|improve this answer

























      up vote
      5
      down vote













      I don't know what "best practices in terms of style" in the answer by @8kb (at the time of writing) means. Certainly some of the listed items ("Table names are not pluralized", "No underscores", etc) are mere style choices which are obviously subjective. I would have thought the personal preferences of the documentation team lead would be the greatest factor here.



      As regards heuristics in SQL in general (as opposed to proprietary SQL such as T-SQL), there is but one book on the subject: Joe Celko's SQL programming style.Many of the choices for SQL Server's AdventureWorks database conflict with Celko's guidelines.



      Celko's naming convention is based on on the international standard ISO 11179 e.g. specifies that a delimiting character (such as an underscore) should be used to separate elements in a name. Other style choices are similarly backup up by research e.g. using exclusively lower case letters for column names so aid scanning by the human eye. No doubt there are subjective personal preferences in there too but they are based on many years of experiences out in the field.



      On the plus side, things have improved in the SQL Server docs in recent years e.g. SQL keywords capitalized, semi-colons to separate statements, etc. Adventure works is a vast improvement on Northwind and pubs. Now why can't the scripting feature in Management Studio spit out code that is a little easier on the eye?!






      share|improve this answer























        up vote
        5
        down vote










        up vote
        5
        down vote









        I don't know what "best practices in terms of style" in the answer by @8kb (at the time of writing) means. Certainly some of the listed items ("Table names are not pluralized", "No underscores", etc) are mere style choices which are obviously subjective. I would have thought the personal preferences of the documentation team lead would be the greatest factor here.



        As regards heuristics in SQL in general (as opposed to proprietary SQL such as T-SQL), there is but one book on the subject: Joe Celko's SQL programming style.Many of the choices for SQL Server's AdventureWorks database conflict with Celko's guidelines.



        Celko's naming convention is based on on the international standard ISO 11179 e.g. specifies that a delimiting character (such as an underscore) should be used to separate elements in a name. Other style choices are similarly backup up by research e.g. using exclusively lower case letters for column names so aid scanning by the human eye. No doubt there are subjective personal preferences in there too but they are based on many years of experiences out in the field.



        On the plus side, things have improved in the SQL Server docs in recent years e.g. SQL keywords capitalized, semi-colons to separate statements, etc. Adventure works is a vast improvement on Northwind and pubs. Now why can't the scripting feature in Management Studio spit out code that is a little easier on the eye?!






        share|improve this answer












        I don't know what "best practices in terms of style" in the answer by @8kb (at the time of writing) means. Certainly some of the listed items ("Table names are not pluralized", "No underscores", etc) are mere style choices which are obviously subjective. I would have thought the personal preferences of the documentation team lead would be the greatest factor here.



        As regards heuristics in SQL in general (as opposed to proprietary SQL such as T-SQL), there is but one book on the subject: Joe Celko's SQL programming style.Many of the choices for SQL Server's AdventureWorks database conflict with Celko's guidelines.



        Celko's naming convention is based on on the international standard ISO 11179 e.g. specifies that a delimiting character (such as an underscore) should be used to separate elements in a name. Other style choices are similarly backup up by research e.g. using exclusively lower case letters for column names so aid scanning by the human eye. No doubt there are subjective personal preferences in there too but they are based on many years of experiences out in the field.



        On the plus side, things have improved in the SQL Server docs in recent years e.g. SQL keywords capitalized, semi-colons to separate statements, etc. Adventure works is a vast improvement on Northwind and pubs. Now why can't the scripting feature in Management Studio spit out code that is a little easier on the eye?!







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Oct 28 '11 at 7:41









        onedaywhen

        42.8k1076121




        42.8k1076121






























             

            draft saved


            draft discarded



















































             


            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f3593582%2fdatabase-naming-conventions-by-microsoft%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Florida Star v. B. J. F.

            Danny Elfman

            Lugert, Oklahoma