Transact-SQL as a Programming Language

Is Transact-SQL really a programming language? On the one hand, Microsoft doesn't claim that Transact-SQL is an alternative to C, C++, Microsoft Visual Basic, COBOL, Fortran, or such 4GL development environments as Sybase PowerBuilder or Borland Inprise Delphi. You'd be hard-pressed to think of a substantial application that you could write entirely in Transact-SQL, and every one of those other languages or tools exists to do exactly that. Also, Transact-SQL offers no user interface and no file or device I/O, and the programming constructs are simple and limited.

On the other hand, you can argue that Transact-SQL is a specialized language that is best used in addition to one of those other languages or tools. It allows SQL Server to be programmed to execute complex tasks such as declaring and setting variables, branching, looping, and error checking, without the need for code to be written in another language. You can write reusable routines that you subsequently invoke and pass variables to. You can introduce bugs if you make an error in logic or syntax. Your Transact-SQL code can quickly get so complicated that you'll be glad you can use the same debugger to debug Transact-SQL routines that you use for C, C++, and Java development.

With Transact-SQL routines, conditional logic executes within the SQL Server engine and even within an executing SQL statement. This can greatly improve performance—the alternative would be message passing between the client process and the server. In today's increasingly networked world, reducing round-trip conversations is a key to developing and deploying efficient applications. Some might claim that the emergence of the Internet makes client/server computing irrelevant. But in fact, the typically slow network conditions found in Internet applications make client/server computing more important—operations should be written so that the client/server conversation is minimized. The best way to do this is to use the programming aspects of Transact-SQL to let whole routines execute remotely, without the need for intermediate processing at the client. The corresponding performance gains are phenomenal.

In addition, by having SQL Server, rather than the application, handle the database logic, applications can often better insulate themselves from change. An application can execute a procedure simply by calling it and passing some parameters, and then the database structure or the procedure can change radically while the application remains entirely unaffected. As long as the inputs and outputs of the procedure are unchanged, the application is shielded from underlying changes to the database. The ability to encapsulate the database routines can result in a more efficient development environment for large projects in which the application programmers are often distinct from the database experts.

Programming at Multiple Levels

In the early days of database management systems, a single computer handled all processing. This was what we now call the one-tier model. The advent of client/server computing introduced the classic two-tier model: a server receiving requests from and providing data to a separate client. The newest approach, the three-tier model, places an intermediate layer between the database server and the client application.

Figure 10-1 shows the two-tier client/server model, and Figure 10-2 shows the three-tier model, which some programmers find preferable for some applications.

Figure 10-1. The two-tier model.

Figure 10-2. The three-tier model.

The two-tier model has a client (first tier) and a server (second tier); the client handles application and presentation logic, and the server handles data services and business services. The two-tier model uses the so-called fat client—the client does a large amount of application processing locally. Many solutions are deployed using this topology; certainly the lion's share of today's non-Internet client/server solutions are two-tier.

In the three-tier model, a thin client handles mostly presentation tasks. Supporters of the three-tier approach point out that this model allows the client computer to be less powerful; allows a variety of client operating environments (obviously the case with Internet browsers); and reduces the complexity of installing, configuring, and maintaining software at the client. The client connects to an application server that handles the application process. The application server then connects to the database server, handling data services. The application server is typically a more powerful machine than the client, and it handles all the issues of application code maintenance, configuration, operations, and so on.

Presumably, far fewer application servers exist than clients; therefore, server costs and issues should be reduced in the three-tier model. (The three tiers are logical concepts; the actual number of computers involved might be more or less than three.) Typically, you're part of a three-tier model when you use the Internet with a browser and access a Web page residing on an Internet server that performs database work. An example of a three-tier application is a mail-order company's Web page that checks current availability by using ODBC calls to access its inventory in SQL Server.

Both two-tier and three-tier solutions have advantages and disadvantages, and each can be viewed as a potential solution to a given problem. The specifics of the problem and the constraints imposed by the environment (including costs, hardware, and expertise of both the development staff and the user community) should drive the solution's overall design. However, some people claim that a three-tier solution means that such capabilities as stored procedures, rules, constraints, or triggers are no longer important or shouldn't be used. In a three-tier solution, the middle tier doing application logic is still a client from the perspective of the database services (that is, SQL Server). Consequently, for that tier, it's still preferable that remote work be done close to the data. If the data can be accessed by servers other than the application server, integrity checking must be done at the database server as well as in the application. Otherwise, you've left a gaping hole through which the integrity checks in the application can be circumvented.

Throughout the remainder of this book, unless otherwise noted, I'll use the term client from the perspective of SQL Server. The client sends the commands and processes the results, and it makes no difference to SQL Server whether an application server process does this or whether the process that the end user is running directly does it. The benefits of the programmable server provide much more efficient network use and better abstraction of data services from the application and application server.

A Three-Tier Solution That Works

---

Perhaps the single most successful three-tier solution available today is R/3 from SAP. The financial and operations capabilities that R/3 provides use a three-tier model, with a terminal-like interface connected to the R/3 application server that then accesses Microsoft SQL Server. The R/3 application server wisely and heavily uses stored procedures that provide both performance gains and development efficiency.