SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ms standards doc?


ms standards doc?

Author
Message
sqlguy-736318
sqlguy-736318
Right there with Babe
Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)

Group: General Forum Members
Points: 757 Visits: 447
Does Microsoft provide a standards document for SS design standards?

For example: "Column names should be Pascal-cased - ex - FirstName"
Lowell
Lowell
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72484 Visits: 40942
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!
sqlguy-736318
sqlguy-736318
Right there with Babe
Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)

Group: General Forum Members
Points: 757 Visits: 447
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?
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95603 Visits: 38968
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19777 Visits: 7410
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95603 Visits: 38968
Also, don't name your procedures with "sp_" as you may run afoul of a Microsoft system stored procedure of the same name.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19777 Visits: 7410
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
sqlguy-736318
sqlguy-736318
Right there with Babe
Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)Right there with Babe (757 reputation)

Group: General Forum Members
Points: 757 Visits: 447
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?
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95603 Visits: 38968
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19777 Visits: 7410
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search