Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

T-SQL Development Standards Expand / Collapse
Author
Message
Posted Thursday, October 23, 2008 11:41 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 5:03 AM
Points: 847, Visits: 478
I've been tasked with authoring SQL Development standards for my corporation. I am starting with T-SQL and our proposed code review process.

I know there are plenty of conversations around best practices, but I am looking for more distillation than that...a guide (especially for non-declarative language developers) on the consistent ways in which code should be developed that doubles (sort of) in the end as a check-list for code reviewers.

I'm certain at least some of your companies have published development specifications of this sort. What I am looking for at the moment is a cohesive organization for the document. Can anyone lend or point me toward a simple table of contents they are using or know of that I can make use of?


------------
Buy the ticket, take the ride. -- Hunter S. Thompson
Post #590668
Posted Thursday, October 23, 2008 8:07 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 1:47 PM
Points: 478, Visits: 1,427
You might want to read through this post.
I found it helpful.
On the second page Jane posted an excellent standards document.
http://www.sqlservercentral.com/Forums/Topic566838-146-2.aspx


  Post Attachments 
DatabaseNamingConventions_v1.1.rtf (349 views, 122.79 KB)
Post #590926
Posted Saturday, October 25, 2008 5:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 35,772, Visits: 32,445
Heh... I don't know why people even bother with any standards at all. As soon as something needs to "go in in a hurry", all notions of standards and well intended code reviews will go by the wayside.

I've attached some standards I wrote for the last company I was at. They're not simple... it covers everything except naming conventions for columns. I used to review every single piece of code that went anywhere near the database. It was a monumental but very effective job. The quality of code went from -50 to 9 on a scale of 1 to 10. No, that's not a misprint... the code was absolute crap, totally unreadable, horribly difficult to modify, full of RBAR, caused an average of 640 deadlocks per day (also not a misprint), and multiple 24-40 hour jobs dropped in duration to an average of less than 30 minutes.

But, ya gotta be will to and have management's support to just say "NO" to bad code.

Unless someone comes right out and says "These are the corporate standards and NO code will be promoted unless it meets all of the standards, including required documentation and performance/scalability tests. Your raises and, possibly, your demotions depend on how well you accept and meet these standards", no one will listen and you'll still end up with crap code.

There's a couple of changes that I actually need to make to the attached standards, but these standards were developed based on things that people did wrong at my previous company. Some of them seem to be just "stupid" because they seem like common sense... but I've found that common sense isn't so common especially in the presence of reluctent/arrogant developers and people trying to meet a schedule at any cost.

My very best wishes to you on trying to establish standards especially where embedded documentation and format are concerned. It's well worth the gargantuan effort to make the paradigm shift. Plan on becoming both the most loved and the most hated person in the company.

I've changed all the 3 letter references from my old company to "XYZ"...

{EDIT} I've removed the standards document that I had previously attached because it was seriously out of date for what we know today. For example, because of the wishes of my employer at the time, the use of WITH(NOLOCK) was not only condoned, but required in almost every case. Hopefully, we all know better than that today.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #591749
Posted Monday, October 27, 2008 1:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 12:43 PM
Points: 211, Visits: 585
I second Jeffs sentiments. I had to do this aswell, and without management support DB standards get nowhere. Especially in the face of non DB developers and Administrators who think the few chapters they did in their web development course is all there is to DB work.

Now go forth and fight the good fight for the DBA's :D

GOOD LUCK



Post #591941
Posted Wednesday, November 5, 2008 9:05 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 5:03 AM
Points: 847, Visits: 478
Jeff,

Thanks for the information. I'm also digesting a couple of books from Mr. Celko that apply.

As far as the support goes, this wasn't my idea. The idea came out during a meeting between the corporate DBAs, me (they consider me a logical DBA. I've done a lot of administration but do primarily development. They don't develop.) and the person the corporate DBAs report to.

We, too, have a lot of crap code and I've spent a lot of time rewriting it. We have had standards within our group, but I was asked to develop something more encompassing that would be corporate-wide. The project has the blessing of the CIO and the supervisor of the DBAs (who reports to the CIO) is willing to push the structure and compliance, in part by designating code reviewers in each location and establishing round-robin code review.

Obviously I am not interested in copying someone else's work but I don't want to completely invent it myself, either. But I was pretty certain that I would get something valuable from this forum.

Thanks to all.


------------
Buy the ticket, take the ride. -- Hunter S. Thompson
Post #597438
Posted Wednesday, November 5, 2008 5:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 35,772, Visits: 32,445
Thanks for the feedback, Bryant.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #597783
Posted Wednesday, February 17, 2010 12:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 7, 2014 10:49 AM
Points: 8, Visits: 37
I understand this is an old forum, but in effort to add to a discussion that others may benefit from I want to share a document that might also help. I found the attached standards guide on a search for a similar topic. The link is www.cms.hhs.gov/dbadmin/downloads/sqlserverstandardsandguildelines.pdf. It appears to have been developed for the US Dept of Health & Human Services CMS group. But I think its a simple plain language example of a best practices document.



  Post Attachments 
SqlServerStandardsAndGuildelines.pdf (363 views, 163.96 KB)
Post #867394
Posted Monday, November 24, 2014 8:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 9:32 AM
Points: 1,417, Visits: 809
Reading 'XYZ', I'm surprised that the use of views is discoraged.
Post #1638611
Posted Monday, November 24, 2014 8:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 13,328, Visits: 12,825
paul s-306273 (11/24/2014)
Reading 'XYZ', I'm surprised that the use of views is discoraged.


You read that document (which btw is more than 6 years old) and you were surprised about the use of views being discouraged?

How about some of the other interesting things in there (no offense meant Jeff):

All the object prefixes sounds like a blast from the past. I would be surprised if that were something you would put together today as object prefixes tend to make things more difficult.

How about this one?


1. All tables (except temp tables and table variables) and views that are not the target object of an Update, Insert, or Delete statement, must be accompanied by the WITH (NOLOCK) optimizer hint. The use of (NOLOCK) without a WITH is discouraged.


That does not sound like something that Jeff would put his name on today.

All in all it is a great example of how to put this together but I would be careful about just grabbing that document today and using in any environment.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1638621
Posted Monday, November 24, 2014 3:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 35,772, Visits: 32,445
Sean Lange (11/24/2014)
paul s-306273 (11/24/2014)
Reading 'XYZ', I'm surprised that the use of views is discoraged.


You read that document (which btw is more than 6 years old) and you were surprised about the use of views being discouraged?

How about some of the other interesting things in there (no offense meant Jeff):

All the object prefixes sounds like a blast from the past. I would be surprised if that were something you would put together today as object prefixes tend to make things more difficult.

How about this one?


1. All tables (except temp tables and table variables) and views that are not the target object of an Update, Insert, or Delete statement, must be accompanied by the WITH (NOLOCK) optimizer hint. The use of (NOLOCK) without a WITH is discouraged.


That does not sound like something that Jeff would put his name on today.

All in all it is a great example of how to put this together but I would be careful about just grabbing that document today and using in any environment.


That last one was because that was a standard heaped on me by an employer. I didn't know enough about it back then to know how bad that little suggestion actually was.

The point that I believe that you're trying to make is a great one. Understanding changes faster than the actual code and what was right yesterday might not be today... and I whole heartedly agree.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1638725
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse