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 PowerBuilder or 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 still argue that, yes, Transact-SQL is a specialized language, which is best used in addition to one of those other languages or tools. It allows SQL Server to be programmed to execute complex tasks requiring branching and looping without the need for code to be written in another language. Transact-SQL provides the services to declare and set variables, to branch, to loop, and to check errors. 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 will be glad you can use the same debugger you use for C, C++, and Java development to debug Transact-SQL routines.
With Transact-SQL routines, conditional logic executes within the SQL Server engine and even within an executing SQL statement. This can greatly improve performancethe alternative would be message-passing between the client process and the server. In today's increasingly networked world, reducing round-trip conversations is 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. Being able 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.
In the early days of database management systems, a single computer handled all processingwhat we now call the one- tier model . The advent of client/server computing introduced the classic two-tier model : a server that receives requests from and provides data to a separate client. The newest approach, the three-tier model, places an intermediate layer between the database server and the client application.
The three-tier model has gained supporters who view the model as preferablefor some applicationsto the more traditional two-tier client/server model. Figures 9-1 and 9-2 show both models.
Figure 9-1. The two-tier model.
Figure 9-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 deployed use 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 different 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 has 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.)
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. (R/3 supports several different database systems, but here we're emphasizing the solution's topology when using SQL Server.) The R/3 application server wisely and heavily uses stored procedures that provide both performance gains and development efficiency.
Throughout the remainder of this book, unless otherwise noted, we'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.