Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

Design Elements Part 5: Where Does The Code Live?

Last updated Mar 28, 2003.

The interesting part of writing code using T-SQL is that it isn't really a full programming language; it's a platform programming language. That means that important programming concepts, such as User Interface design and file manipulation aren't part of what T-SQL handles. In that aspect, Transact-SQL isn't a "full" or complete programming language.

On the other hand, T-SQL does have more than the basics of a programming language. As we've seen already, it contains statements and operators, code blocks, scope, error handling and variables. In upcoming tutorials, we'll cover more about the best practices to follow when using Transact-SQL to code.

Right now, it's most helpful to look at the T-SQL constructs that are part of "full" programming and what are not. While the chart below is by no means complete, it does show the major concepts and whether T-SQL implements them:

Concept or Construct:

T-SQL Implementation:

Recursion

Can be implemented in T-SQL code

Arrays

Can be implemented with tables or table variables

Casting

CAST and CONVERT

Statements

Data Manipulation and Data Definition

Code Blocks

Each transaction is a block, plus BEGIN and END statements

Decision Trees

Inherent

Scanning

Inherent

Search

Inherent

Sorting

Inherent

Program Flow

Manual – could be implemented by nested calls

Operators

Mathematical, Equivalency, Boolean, Statistical

Object handling

Minor

OS integration

Minor

Variables

Multiple Types, Local scope only

Device control

N/A

File Manipulation

N/A

Graphics

N/A

Pointers

N/A

Print control

N/A

Queues

N/A

Stacks

N/A

User Interface

N/A

Functions

Not the same as programming functions, more akin to stored procedures

String Manipulation

Single and Multiple Character

Debugging

Yes

Syntax Parsing

Yes

Error Handling

Partial

Lexical Units (literals, comments, delimiters)

Yes

Declarations

Yes

IF-THEN-ELSE

Yes

Iterative Loops

Yes

Collections

N/A


If T-SQL isn't a complete language, shouldn't it be considered part of a pure developer's job to learn the best way to write stored procedures and dynamic SQL? Is there really a need for a "Developer-DBA"? The answer is yes – to both questions.

Some developers never have to bother with much database interaction. Game designers, statistical programs and the like have minimal contact with databases. In those cases, the developer won't have a lot of experience or desire to focus on database constructs.

In other cases, business system developers certainly need to understand how to write code against a database system. Even then, however, there are levels of SQL Server knowledge required that are outside the time and skill constraints of the average developer.

In both of these cases, there is certainly a need for a skilled DBA to know how to program. In addition, while a developer might have a good deal of T-SQL knowledge, writing code that behaves well in a multi-database system is another thing.

In addition, the Developer-DBA is left to hone her T-SQL skills, giving an advantage in knowledge. This allows the "distribution of labor" concept that works on many levels.

So if there is a difference between these two developers, when do we draw the line between things that are written in a specific language (such as C# or Java) and those written in T-SQL? Put another way, where should the processing take place: at the client or middle-tier or at the SQL Server level?

A simple answer might involve merely comparing the feature we need with the ones SQL Server provides (as shown on the earlier chart). You use T-SQL where it has an advantage and the other language where it's better suited to handle the task.

Using this method leaves us with a few gaps, however. For instance, SQL Server is great at sorting (it does that using the ORDER BY statement), not as great with scope on variables. If these two constructs are needed in tandem, it might not even be possible to create the proper statements and handoffs.

The other method of deciding where to place the code is to make a decision at the outset about where the T-SQL will reside. When T-SQL code is written into the programming language on the client or in the middle-tier and then sent to be processed by SQL Server, it's called Dynamic SQL. The alternative is to code the T-SQL into Stored Procedures, and then call those procedures from the client or middle-tier.

The advantage of this approach is encapsulation. In this method, the programming language calls a Stored Procedure, providing inputs and accepting outputs. The T-SQL inside the Stored Procedure can be maintained and optimized separately, leaving the programming language to handle things that T-SQL does not.

Another advantage to this approach is that the Stored Procedures aren't compiled into an executable file, so they can be edited without a recompile. If you upgrade SQL Server to a newer version, the Stored Procedures can be optimized for that platform and dropped into place without changing a single line of programming code on the client or middle-tier.

Finally, this approach allows SQL Server to cache the Stored Procedures, making them faster. What this means is that the query engine can evaluate the T-SQL and develop the most efficient plan to execute the commands. When that procedure is called for again, the plan is already available and acted on even more quickly.

While these advantages are quite compelling, it's a bit too simplistic to set one method into place blindly. There are conditions which call for either approach, or even a mixture of both.

In any case, it's important to figure out from the outset what the goal is. The T-SQL code is only part of any larger design, and it's important to think in terms of an entire system.

Business processes and rules, budget constraints, design goals (and other criteria) will determine, in large part, where to place the T-SQL code.

After we cover the concepts and mechanical parts of T-SQL programming, we'll discuss the process to create an entire system. Stay tuned – we have a few things to learn before we put it all together.

Online Resources

Erland Sommarskog has a really good treatment of Dynamic SQL and Stored Procedures where he covers the concepts I've mentioned here a bit deeper. It's a good resource.

InformIT Tutorials and Sample Chapters

Ready to jump ahead and learn a lot more about software design? Thomas J. Mowbray and Raphael Malveau have a great reference on Safari called "Software Architect Bootcamp, 2nd Edition."