Working with Developers

Question

Working with Developers

joshcsmith13

Hall of Fame

Points: 3381
More actions
April 26, 2006 at 9:46 am

#365994

I've been assigned the task of providing some SQL guidelines for a development team of nearly 20 programmers. I've already gleaned some things I like from the Coding Standards (1&2) articles that Steve posted on this site a while ago, and I'm thinking of picking through Bill Wunder's extensive list (http://www.nyx.net/~bwunder/dbChangeControl/standard.htm), but I'm open to suggestions if any body has a particular list that they find useful. Keep in mind that this is going out to a variety of people - many of whom have never had any formal SQL training.
On a related note, the following has been brought up several times, much to my chagrin. Somebody will find that one of our reports is running too slow or is providing incorrect results because the developer who slapped the view or stored procedure together didn't know what he or she was doing. It has always been my stance that, given the immense volume of work I'm dealing with, I don't have time to review every single select statement or read-only stored procedure that somebody whips up to satisfy the latest user request. I focus on solid data modeling, data integrity, and ensuring that we've got a secure, reliable environment. At least I know that there wasn't any data loss or non-recoverable situation going on. Now, when somebody finds a query that could use a little tuning, I'm happy to help out and make some tweaks. I just don't have time to be proactive in that regard. Another DBA to share the work would be great, but I dont' see that happening any time soon.
What do you think?

Viewing 9 posts - 1 through 8 (of 8 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

I just finished putting together a T-SQL standards document for our developers to use. The following sites have some good info to consider:

http://www.sql-server-performance.com/vk_sql_best_practices.asp

http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLServerDatabases.aspx

http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsqlpro04/html/sp04l9.asp

I agree with you with what you've said regarding not being able to review every stored procedure that comes through. If you have developers writing stored procedures, you cannot look at each one and verify that it produces the correct results. This would require you to not only unit-test all t-sql changes that come through, but you would also have to review the specifications for each change as part of the process. The developers should be the ones to unit test their t-sql code and if there is concern with inaccurate results, the t-sql should then be sent to a QA or testing department who would be responsible for verifying that the developers are producing reliable code.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Pam Brisjar SSChampion Points: 12094 More actions · Answer 2

John's hit my favorite sites, besides this one. There are a ton of articles here on SSC that speak to standards and practices. Here's a few of my favorites:

http://www.sqlservercentral.com/columnists/dhatheway/twobestpractices.asp

http://www.sqlservercentral.com/columnists/nboyle/speed_select.asp

http://www.sqlservercentral.com/columnists/cherring/replacingcursorsandwhileloops.asp

And agreed on the review standards. There's only so much time in a day and only so many resources available. One has to pick one's battles.

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 3

thanks guys (and gal)!

funny, I had already seen most of these websites when I searched, but it's good to know I'm not way off base. What's hard now is trying to pare this all down to a managable document that a developer might actually read!

Greg Uzelac Old Hand Points: 311 More actions · Answer 4

These links and hints were very helpful to me. Thanks you!

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 5

I forgot to include one more thing in the original post...

How many full-time application coders can a development DBA support? I'm curious to know what you guys are dealing with.

John Rowan SSC Guru Points: 56440 More actions · Answer 6

I think that has alot to do with the developer's comfort/talent level with SQL. Our programmers have varying degrees of skill with SQL so some need more support than others. We have 2 full time DBAs and around 15 programmers. We (the DBAs) spend the majority of our time optimizing code, writing custom scripts/conversions and upgrades, database change management and other tasks and spend maybe 1/4 of our time supporting programmers.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

joshcsmith13 Hall of Fame Points: 3381 More actions · Answer 7

Thanks John. I like that answer! You bring up a very valid point too. A DBA does not simply support the programmers. There are numerous data-based projects that you've got to handle too. What about Change Management though? Do you keep db scrpts in a source control system? Do your programmers have access to that, or do the DBAs handle all traffic (or at least additions and modifications) into source-control? We're trying to establish processes at my company right now, and this is a question I'm having trouble answering.

John Rowan SSC Guru Points: 56440 More actions · Answer 8

We are also in the process of standardizing our processes. We currently do not have all of our database objects in a source/version control software, but we are moving in that direction. We do not have anything formal in place now, but I envision a structure where developers can check out and modify stored procedures and functions, but only DBAs have access to tables (including indexes, triggers, constraints), views and users. We are looking into using DB Ghost now to help with builds (from our source control system) and automated upgrades.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden