Manage Learn to apply best practices and optimize your operations.

SQL Server development shop best practices

Successful SQL Server development shop best practices, such as creating stored procedures, are explained by Hilary Cotter.

In a perfect world, SQL Server development procedures are documented, later becoming specifications and best practices...

for others to follow. In larger development shops, however , there is a lot of room for documentation to fall through the cracks. You may find a dedicated SQL developer or even a SQL development team as well as a data architect (or data modeler), who is responsible for designing the logical and physical data models. The SQL development team often works independently or asynchronously from the main application development team.

SQL developers are often the last to deliver their code to the build process -- not due to their tardiness or the complexity of the work, but because the application requirements or specs are in a state of flux. The flux is generated when the application developers discover architectural problems in the application design -- and they frequently require application design changes, which gradually trickle down to the SQL developers.

Talk to the spec

SQL developers will meet with the architects and agree on stored procedure names, parameters, data types, return codes and the required functionality of the stored procedures. In successful SQL development shops, this is all documented in a specification (called a spec) and put through a change management process to prevent scope creep and stop the development process from getting out of control.

It is common in the software industry to stub classes or functions with the property, event or method name, as well as the parameters accepted, return codes and so on. But no code exists behind the class or function calls. Stubbing encourages modularity and allows different developers to work simultaneously on different parts of the code -- no developer has to wait on another developer to complete his code. As development progresses, the developers flesh out the code behind the stubs and they become full-fledged classes or functions. Sometimes the code behind classes or functions is commented out as the product is released to manufacturing and features are cut because of supportability concerns or bugs. But the stubs remain.

It's a good practice to name the stored procedures after the function names calling them and to have a single stored procedure behind each Web page form. This will reap huge dividends during the debugging effort.

Data modeling for everyone

The data architect will create the logical and physical design of the database and provide the SQL developers with table and column names and describe how the tables relate to each other. Data architects use data modeling tools and frequently encapsulate their designs in Unified Modeling Language (UML) diagrams, which are easily understood by everyone in the development process. Search on "data modeling tools" on Google to find some of the commercially available data modeling tools. The SQL developers will then start coding.

SQL development rules and regulations

Once the spec is finalized, the SQL developers start to work. Before opening up Query Analyzer or another SQL development tool, the SQL code is generally blocked out according to data flow and functionality. This creates modularity and encourages code reuse. It also breaks down large monolithic stored procedures into many smaller stored procedures, which offers better performance.

SQL developers require the following:

A development tool integrated into version control software. It is absolutely critical to use tools integrated into version control software that are highly disciplined. They check the objects they create in and out of the version control software.

Tools that generate representative data to test with. While these tools are commercially available, SQL developers frequently generate their own. It is necessary to generate enough data so indexes will be used by the Query Optimizer in SQL Server -- this could be as much as 100 pages of data.

The correct version of the RDBMS to be used in production and QA. For instance, SQL Server Standard Edition does optimize queries using indexed views the same way as SQL Server Enterprise.

A development machine with enough horsepower to make the development process less of a chore, and to mimic some advantages the RDBMS will make of multiprocessor machines. This is less of a concern today with hyperthreading.

Test environments that allow end-to-end functionality. Frequently these environments are built using products like Virtual Server 2004 or VMWare.

A well-defined set of case studies or data sets that demonstrate all aspects of the functionality required in the spec.

A set of data that will attempt to break the stored procedure or function to test for defensive programming.

The latter two requirements are necessary to build regression tests that will typically be run as part of the build process.

The build process: pass or fail

After development is completed and "dropped," the build process starts, during which, typically, the developers build the complete application. This may involve complete database creation and loading or an upgrade in place of an existing test or QA database. It is critical that no one object break the build process. Many managers require individual developers to test their code before checking it in. Developers take great care to create minibuilds on a department level to ensure that the department's code will not break the build process. Some managers will find creative ways to "punish" offenders who break the build. They do that partially in jest, but breaking the build can be a very expensive process and cause significant deliverability delays. You can buy many build suites commercially, and some are available via open source.

Regression testing, then more tests

After the product is successfully built, it is put through regression tests, where a "robot" will run scripts that test all code functionality. This is where the case studies prove valuable. Regression testing not only tests to ensure that the new development will not break existing functionality, but it also tests new development. Once the new development successfully passes regression testing, it is sent to QA for more intensive functionality and performance testing.


In this article, we looked at the process of software development according to well-established principles. If a development shop follows these principles and creates an environment accordingly, then software and SQL development will be far more efficient. Process is everything.

About the author:
Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and subsequently studied economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.

Next Steps

Designing an access control strategy for the registry

XP SP2 helps control malware -- but watch out for that firewall

Checklist: Tighten default settings to prevent unauthorized access

Dig Deeper on SQL Server Database Modeling and Design

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.