T-SQL Development Standards

Question

T-SQL Development Standards

Viewing 5 posts - 16 through 19 (of 19 total)

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Jeff Moden (11/24/2014)
chenke (2/17/2010)
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.
It might be the machine that I'm on but neither link is working for me.

The attached file works for me. The link is not correct. This should work http://www.cms.gov/Research-Statistics-Data-and-Systems/CMS-Information-Technology/DBAdmin/downloads/sqlserverstandardsandguildelines.pdf

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

MMartin1 One Orange Chip Points: 27771 More actions · Answer 2

Jeff Moden (11/24/2014)
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.

It was required of me by an employer once as well. I could not convince them that this NOLOCK was a bad practice as they were very strict with their standards. So I would not overdo it on standards as well realising that things do evolve. Code reviews I like though, you can quickly see unclean code as it comes.

----------------------------------------------------

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 3

Jeff Moden (10/25/2008)
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.

+1

Don Simpson

I'm not sure about Heisenberg.

MMartin1 One Orange Chip Points: 27771 More actions · Answer 4

DonlSimpson (12/2/2014)
Jeff Moden (10/25/2008)
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.
+1

You may even get an unscrupulous manager that will fault you later for not being perfect in a hurry up environment. I have witnessed these fiascoes.

----------------------------------------------------

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 5

MMartin1 (12/2/2014)
DonlSimpson (12/2/2014)
Jeff Moden (10/25/2008)
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.
+1
You may even get an unscrupulous manager that will fault you later for not being perfect in a hurry up environment. I have witnessed these fiascoes.

I've seen that happen but it won't happen in the shop I'm working in now because... I wrote the standards, I have the authority to enforce them, and never need to fight about it because the managers all know better (I've died and gone to heaven :-P). Make no doubt about it that when we have a real live "urgency" that we get the job done much more quickly but we still don't take any shortcuts. We still do a code review, we still do QA, we might still do a UAT test, and then it goes to production and everything about it has followed the same route as all other code (including documentation, source controlled, etc) but much more quickly because everyone knows that when it's their turn up to bat, they drop everything else to get the job done now.

Very fortunately and to use your good words, there's no way for an unscrupulous manager to fault me or anyone else (I DO protect the Developers and they protect me) because we always follow the given steps like a well oiled machine... just in a more dedicated fashion for "urgencies". What's really cool is that we probably could get away with murder but everyone (including the managers, thank goodness) watches out for each other and the project. It's absolutely the best team (Devs, DBA, Managers, NetOps) I've ever worked with.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)