ms standards doc?

  • Does Microsoft provide a standards document for SS design standards?

    For example: "Column names should be Pascal-cased - ex - FirstName"

  • there's more than a few great threads here on SSC on the subject: i think all of the threads here have actual documents attached from various posters as well.

    http://www.sqlservercentral.com/Forums/Topic590668-338-1.aspx

    http://www.sqlservercentral.com/Forums/Topic857703-391-1.aspx

    http://www.sqlservercentral.com/Forums/Topic560087-145-1.aspx

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Based on my initial scan of the threads the threads don't appear to reference any official MS standards.

    MS provides an automated tool named FXCop to check a .NET/C# codebase for code that doesn't comply to MS standards.

    I guess MS doesn't provide anything similar for SS?

  • sqlguy-736318 (9/19/2012)


    Based on my initial scan of the threads the threads don't appear to reference any official MS standards.

    MS provides an automated tool named FXCop to check a .NET/C# codebase for code that doesn't comply to MS standards.

    I guess MS doesn't provide anything similar for SS?

    No, there isn't.

  • I think MS had that at one point but they dropped it.

    In general, carefully determine rules among yourselves and

    most of all, consistently follow them.

    FWIW, here are my suggested rules:

    Avoid special characters, obviously, and do not start any name with a digit; underscores are OK, of course. In fact, I prefer them to "MixedCase" naming == mixed_case .

    DO NOT prefix table names with "tbl" or the equivalent. It's unnecessary, and could be false, since if the table changes to a view you won't rename it anyway.

    Table names are plural. Technically, I think singular names are more theoretically accurate, but the overwhelming number of table names are plural, so I swim with the river.

    Same rules for views as tables, for the same reasons.

    DO NOT use a table prefix on column names. For example, on the orders table: NOT ord_id, ord_date, ord_cust INSTEAD JUST id, order_date, customer, etc..

    DO NOT put the (abbreviated) data type in a column name, except as part of a normal column name.

    So, no intId or dttmOrderDate.

    But order_date as column name is OK, of course, since "date" is inherently descriptive of the column data itself.

    Decide on rules for naming table-related items -- indexes, constraints, triggers, etc. -- and stick to them. I prefer the "full_table_name__{CL|DF|IX|TR...}" as a prefix to all table "parts" (I think MS's practice of a "PK_" prefix makes NO sense).

    But use any rules for that that meets your needs, JUST ENFORCE IT.

    Policy-based mgmt is good for that, but you can even use after-the-fact queries to validate object names.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Also, don't name your procedures with "sp_" as you may run afoul of a Microsoft system stored procedure of the same name.

  • Lynn Pettis (9/19/2012)


    Also, don't name your procedures with "sp_" as you may run afoul of a Microsoft system stored procedure of the same name.

    Quite true! In fact, don't start ANY name with "sp_".

    Or "sys".

    You might also want to avoid table/view names starting with any of these:

    filestream

    MS

    queue

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • I think it's unfortunate that MS doesn't provide an automated tool to review a SS database for standards compliance. The nice thing about FXCop is that it's somewhat easier to convince an organization to use FxCop to enforce "Microsoft Standards".

    Is there an automated SQL standards tool available that validates databases based on rules published by some other recognized SQL standards organization?

  • sqlguy-736318 (9/19/2012)


    I think it's unfortunate that MS doesn't provide an automated tool to review a SS database for standards compliance. The nice thing about FXCop is that it's somewhat easier to convince an organization to use FxCop to enforce "Microsoft Standards".

    Is there an automated SQL standards tool available that validates databases based on rules published by some other recognized SQL standards organization?

    First, whose standards.

    Second, none that I know of.

  • sqlguy-736318 (9/19/2012)


    I think it's unfortunate that MS doesn't provide an automated tool to review a SS database for standards compliance. The nice thing about FXCop is that it's somewhat easier to convince an organization to use FxCop to enforce "Microsoft Standards".

    Is there an automated SQL standards tool available that validates databases based on rules published by some other recognized SQL standards organization?

    Once you decide on the rules you want, you can put them into PBM and it can check and/or enforce them for you.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • sqlguy-736318 (9/19/2012)


    Does Microsoft provide a standards document for SS design standards?

    For example: "Column names should be Pascal-cased - ex - FirstName"

    Have you ever looked at the databases created by Microsoft? No, they don't have a standard. Or a set of best practices.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • sqlguy-736318 (9/19/2012)


    I think it's unfortunate that MS doesn't provide an automated tool to review a SS database for standards compliance. The nice thing about FXCop is that it's somewhat easier to convince an organization to use FxCop to enforce "Microsoft Standards".

    Is there an automated SQL standards tool available that validates databases based on rules published by some other recognized SQL standards organization?

    There's no complete mechanism that I know of. You can use Policy Based Management as was stated above, but that's a ton of work. You can also look at something like SQLCop which captures quite a few industry recognized standards and best practices. I review it here[/url]. For formatting while you're writing your code, you might take a look at my company's software, Red Gate SQL Prompt[/url]. None of these is a complete standards enforcement solution, but between them you can probably get things set up how you like.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • sqlguy-736318 (9/19/2012)


    I think it's unfortunate that MS doesn't provide an automated tool to review a SS database for standards compliance. The nice thing about FXCop is that it's somewhat easier to convince an organization to use FxCop to enforce "Microsoft Standards".

    Is there an automated SQL standards tool available that validates databases based on rules published by some other recognized SQL standards organization?

    MS has come a long way since the initial release of VS 2008 Team Edition for Database Professionals in aligning the DB professional's development experience with that of the .NET developers. It's been 4 years since then and they haven't completely solved the problem. 80% of managing database code in a structured way is easy to do. The remaining 20% approaches the impossible! C# code analysis is a gift from heaven and I have been wanting them to bring that comprehensive of an experience to the SQL development environment. It actually facilitates learning how to code well! Imagine that.

    SQL has a much more ambiguous syntax than does .NET (not to mention .NET has a common intermediate language which is what is actually inspected so that makes things easier). This is not to mention that code analysis tools for SQL are constrained by the fact that more times than not the efficiency of the code is largely dependent on the data.

    Analyzing Database Code to Improve Code Quality (VS 2010)

    In VS 2008 you could write your own Code Analysis rules. In getting SSDT 2012 to market that feature was left behind unfortunately. It seems however that they're moving towards Static Code Analysis across the board, which is good.

    PBM is an option but that will make for some seriously messy SQL code inspecting other (potentially seriously messy) SQL code and the negative feedback only shows up when you try to deploy it.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 13 posts - 1 through 12 (of 12 total)

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