Any relational database server, such as SQL Server 2005, is essentially a data repository. As you discovered in previous chapters, you design a database under the rules of relational database theorythat is, using tables, primary keys, foreign keys, and so on. The SQL Server language is then used to query and update data in the server. There is nothing wrong with this process, for several major applications do just that and nothing more.
However, when you start creating real applications, it becomes apparent that going back and forth to the database takes considerable work. For instance, to simply verify whether a table is empty, you must open a connection, submit a query such as SELECT COUNT(*) FROM MyTable, close the connection, and then compare the first column of the first line of the result set with zero. You must also check for errors.
This seems like too much work for such a simple operation. Not only is there a great deal of programming involved, but performance is not optimum. To perform any operation in a database server, you must go through the network, and the network may be slow. Even a fast network involves some latency to transmit packages. You and others may call the database several times, and each call competes with other calls. As these costs accumulate, performance suffers.
You can easily code this logic and run it in the database server itself by using stored procedures and user -defined functions (UDFs). Both stored procedures and UDFs are defined, at least traditionally, by using the Transact-SQL (T-SQL) language. This language includes all of the SQL Server commands that you already know, plus some procedural language constructs for things such as:
Defining and using variables (DECLARE, SET)
Controlling flow (IF, ELSE, RETURN)
Using loops (WHILE, BREAK, CONTINUE)
Defining a block (BEGIN, END)
Starting with SQL Server 2005, you can also write UDFs and stored procedures by using a Microsoft .NET language, such as Visual Basic.NET. Using Visual Basic.NET in this way will be discussed later in this chapter.