SQL Server Reference Guide

I’ll begin a discussion of writing proper code with an overview of the flow of a database application, and then the most basic of topics: formatting the code to make it readable and commenting it to make it understandable. I’m generalizing here for both high-level languages such as C-Sharp as well as procedural code such as Transact-SQL (T-SQL).In this tutorial I’ll cover an overview of writing code against SQL Server, and then talk a little about proper formatting and commenting.

Every developer should learn to write clean, easily understood code. Sometimes there are times when things are necessarily intricate — as a friend of mine once said, “some things are complicated because they are complicated” — and at those times comments are essential to document to those who inherit your code to understand what you were thinking. Even if the code is incorrect, too long, and something you’re afraid will be found wanting, proper commenting will earn the respect of those who fix it. More on that in a moment.

Even if you don’t write high-level language or complex T-SQL code for your systems, you should care about code formatting and commenting. If you’re an administrator, you’ll still write T-SQL code, and perhaps even high-level code, to manage, monitor and maintain your systems, so the same benefits a full-time developer enjoys with proper formatting and commenting will be yours.

The following topics are general in nature. I'll cover each in more depth, as I move through the development cycle. But the place to start talking about writing code is the way you write it. In this overview, I won’t be covering specific high-level code — just the general process for selecting a database connection, object and so on. This will serve as the foundation for writing the C-Sharp and other code I’ll cover in later tutorials.

Development Environments

In some information technology shops, the Database Administrators are responsible for all database objects. Such DBAs are involved with the entire development process, from requirements to specifications. They design the database, create the stored procedures that the developer uses to access data, and might even create the T-SQL code used in the program. The DBAs in this type of shop control the movement of the database, from the development and test servers all the way to production. They control the source code of database objects, and are responsible for keeping version control.

In other shops the developers rule the development domain from stem to stern. The developers create the database, design the database objects, and control the access to the records. Once the development is complete, they throw the completed objects "over the fence" to the DBAs, who control access to testing and production environments. They might not use Transact-SQL at all, but instead rely on abstracted constructs such as LINQ or the Entity Framework in their projects.

This second type of shop is the most difficult for DBAs, as they are often required to debug what they did not design. Many times, they are also responsible for decoding why a poorly performing system is running slowly. They may or may not be responsible for source code control.

And then there's the “mixed” shop, where the developers and DBAs work together to manage database objects, each writing code for various parts of the applications as time, talent and design requires.

Whichever type of shop you find yourself in, it's important to have a sound footing regarding the proper way to write programs using SQL Server. Let's examine the ways you can access data and database objects, and set some ground rules for optimal code.

Before I delve into the T-SQL code, I’ll cover general programming techniques using languages such as C-Sharp, Visual Basic .NET and others. Using these languages, there are four basic steps to write a program using a database:

1. Create a database, recordset, or command object
2. Create a connection object
3. Create or execute a command object and optionally receive the result
4. Close the database object

Although this overview isn’t designed to be a full tutorial on high-level programming, it’s useful to walk through a few basics to understand this four-part flow of an application.

The Database or Command Object

In step one you can instantiate (big word that means open) up a database, recordset or command object. I’ll take a moment to discuss the differences between those objects.

The database object opens a channel to the database, and the objects within it must be specified separately.

If you choose a recordset, a set of rows of data is returned. I normally choose a recordset if I'm only interested in working with data, not database objects.

Finally, there's the command object. With this object, I’m not normally concerned with any data, just in sending a T-SQL command to the server, say for a backup, re-index and so on.

Choosing a Connection Method

There are multiple ways to access data in SQL Server, using many different programs. There is a “stack” of communications methods, and the .NET languages from Microsoft primarily use Active Data Objects (ADO) or ADO.NET. These (and other) communication methods then pass their instructions down to Object Linking and Embedding, Database OLE-DB or Open Database Connectivity (ODBC). OLE-DB and ODBC are often referred to as drivers, since under the covers they are a set of Dynamic Link Libraries (DLLs). The step where you set the library you want to use is in the connection object.

Most of Microsoft's recent programming recommendations encourage the use of OLE-DB drivers, for security and performance considerations. In the articles that follow, I'll use both so that you can see the differences.

The Command Object

One big debate in database development is where to execute SQL commands. It makes a difference whether I run a command in program code or call some sort of database object (such as a view, stored procedure, or trigger) that runs on the server.

If I create a string in a program that looks like this:

	SELECT * FROM pubs
	WHERE au_fname LIKE 'W%'
	ORDER BY au_fname

The process is called "Dynamic SQL." In this case, the string is sent to SQL Server via the connection object; it's executed; and the results are returned to the calling program.

If I create a string that looks like this:

	EXEC usp_MyStoredProcedure 'variable1', 'variable2'

The process is called "Server-Side Code." As with dynamic SQL, the string is sent to SQL Server, but the SQL code is now invoked at the server. This is often the preferred method of calling SQL commands, because only a small string has to be sent to the server. Plus, when stored procedures are used, the server often remembers the way it figured out where the data was, called a procedure cache. If that stored procedure is called again, the server doesn't have to work as hard to find the data. That's always a good thing.

Yet another advantage to the server code method is that when using web-based development, SQL Server is less susceptible to SQL Injection attacks. That's also a good thing.

So it would seem that I should always use stored procedures, views and other server code in our programs, right? Ill, it's not quite that simple. Sometimes it is best to keep your options open. One reason to use Dynamic SQL is when the program builds the user's request on the fly. Single-use statements are another reason you might invoke Dynamic SQL.

Choosing a Language

What is the “perfect language” for writing high-level code against SQL Server? I think there are several world wars that have less controversy than any answer I might give here. And I don’t care to start another one.

A good guideline to use when choosing a programming language is to use the one with which you're most familiar or most comfortable learning. A better guideline is to find the language that handles the problem you're solving in the best way. If you’re using one of the Microsoft .NET languages, they all compile down into IL — a language-independent layer that executes the actual program, so the choice becomes much easier.

Because each language handles the object steps that I mentioned a little differently, you’ll have to learn a little about the process used in each to understand how to move forward in that language.

I'll use pseudo-code to show the processes, loops and other methods in the code, and then I’ll dip down into real-world languages for simple examples. What follows are some best practices that I’ve learn about programming against SQL Server, which are applicable regardless of your preference of language.

With that broad overview of the code process, let’s take a look at the attributes of code that are a best practice whether you’re writing in a high-level language or Transact-SQL.

Commenting Your Code

In my database design tutorials, I began each design with a requirements document. I then specified the results I wanted, and created an Entity Relationship Diagram (ERD) that contained all the objects I would create.

In the development process, you'll find even more processes and controls. There are several ways to model the program, and lots of documentation types to choose from. My series of tutorials won't focus much on these controls, since I’m going to focus on the code that touches the database, not the development process.

There are many ways to code. Some developers jump right in and begin to code, jumping back and forth and fixing syntax as required. Others won't code a line without a full diagram containing process flow, variable declarations, and inputs and outputs. Either way is acceptable, unless you're in a shop that dictates one or the other. In that case, code like your shop codes.

Personally, I "code my comments." That is, I write all the comments about the steps my code will produce first, and then go back and write my code.

This process helps me in two ways. The first advantage is that my code always has comments! Nothing is more irritating to another developer than to maintain code that someone else has written, when the original developer didn't comment his or her code. It's very difficult to figure out what the programmer was thinking when he or she included a tricky algorithm. I've often removed what I thought were extraneous lines of code, only to find the original developer had to do it that way to avoid another issue later. It would be better to know that from the outset.

The second advantage is that creating my comments first helps me avoid "spaghetti" code, which happens when a process isn't thoroughly thought out.

If you're using Dynamic SQL, each language will have its own method of using comments. For Visual Basic, it's the single quote ('); for Java and C-Sharp, you can use two forward-slashes (//).

If you're using Server-side T-SQL code, such as stored procedures, you have two choices for comments: line and block comments.

Line comments begin with two dashes (--) and end with a carriage return. They look like this:

	SELECT * FROM
	authors WHERE 
	-- Using a subselect to limit to published authors
	au_lname IN (SELECT au_lname FROM published)

Line comments are useful since they are normally placed right in the section of code where you need to draw attention.

The second type of comment spans multiple lines. It's called the block comment, and starts with a forward-slash and then an asterisk (/*), and ends with an asterisk and then a forward slash (*/). These comments are used for more descriptive lines of text, but there's no reason I can't use them with the shorter comments as Ill:

	/* I wanted to limit the results to only the
	 authors that were published, so I've used
	 a subselect here. */
	SELECT * FROM
	authors WHERE 
	au_lname IN (SELECT au_lname FROM published)

I find block comments more useful (and safe) than line comments because of the mixed set of text editors my team uses. Putting the code through the various editors sometimes breaks the line comments in the middle of the line, causing an error when the SQL code runs. With block comments, the lines can be broken without causing an error.

Code Formatting

Along with the religious wars over the best programming language, a close second is the best way to format that code. Once again, the best rule is the simplest: Make it easy to read and follow.

This normally involves two things: spacing and tabbing (called whitespace) and line layout. Some code editors (which are sometimes part of an Integrated Development Environment or IDE) will handle this for you. Others, such as SQL Server Management Studio, will not.

There are some overall guidelines you can follow. The first is to keep the screen-width for the code lines as short as practical. While you don’t want four letters of code on each line, it shouldn’t wrap around the screen ten times either. I try and keep the code readable at a 1024X768 full-screen resolution, where possible.

Second, the “whitespace” (space characters or tabs) should follow a consistent pattern. Personally, I like tabs rather than spaces, since they will render consistently, but it’s completely acceptable to use spaces instead — just use them the same way throughout the code.

If there are “block” statements in your language, I normally recommend that you place the block starter in an obvious place for pairing. For instance, assume your language starts a new block with the brace character { and closes the block statement with the opposite facing brace }. You have a couple of choices. The first is that you can stand the block starts and stops on their own line:

{
Block starts here
Block code
I’m done
}

That’s quite readable, but might be a little lengthy. It’s acceptable to put these on the same first line of code or comments, but leave a little space to make the block obvious:

{  Block starts here
Block code
I’m done }

Again, many of these choices are just preference — but the overriding goal is to make the code readable, understandable and consistent.

The final area of formatting is the use of case. I don’t mean the case statement, but the literal “big letter” versus “little letter” — like A versus a. In some languages, case is very important. For instance, the variable $a is different than the variable $A. So in those instances, it’s not a matter of formatting but actual syntax, and of course that dictates everything.

When it isn’t a matter of syntax, I tend to capitalize commands and keywords when I’m writing T-SQL. It differentiates my data from the programming language.

Next is the naming of comments. I like “Camel Case”, meaning a variable named Buck Woody would be called $BuckWoody in C-Sharp and @BuckWoody in T-SQL. Some people REALLY don’t like that — and that’s fine. Again, be consistent, make it understandable and readable.

In the next few tutorials, I'll cover error handling, security, and more.

InformIT Articles and Sample Chapters

Alistair Cockburn has a sample chapter you can read regarding Selecting and Setting Up an OO Project.

Books and eBooks

I’m a big proponent of test-developed software. Check out an excellent book on that, Growing Object-Oriented Software, Guided by Tests.

Online Resources

Rodey Green explains how to write un-maintainable code. Read this, and then do the opposite!