September 19, 2012 at 3:11 pm
Does Microsoft provide a standards document for SS design standards?
For example: "Column names should be Pascal-cased - ex - FirstName"
September 19, 2012 at 3:19 pm
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
September 19, 2012 at 3:29 pm
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?
September 19, 2012 at 3:37 pm
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.
September 19, 2012 at 3:40 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 19, 2012 at 3:44 pm
Also, don't name your procedures with "sp_" as you may run afoul of a Microsoft system stored procedure of the same name.
September 19, 2012 at 3:52 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 19, 2012 at 4:13 pm
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?
September 19, 2012 at 4:22 pm
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.
September 19, 2012 at 4:37 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 20, 2012 at 4:17 am
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
September 20, 2012 at 4:24 am
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
September 24, 2012 at 6:36 pm
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