4 Places Where T-SQL Just Makes Sense

Transact-SQL is well known as the Microsoft and Sybase implementation of the Structured Query Language used by all databases. But what might not be obvious at first glance is the increased set of capabilities that T-SQL brings to the database server. T-SQL is actually a fully procedural programming environment that passes the Turing-completeness test.

So in this sense, T-SQL is more than a proprietary syntax for executing queries. It combines full data processing functionality with the DML statements needed to store, retrieve, and modify information within the database. (There's more about DML and DDL functions in an earlier post.) At first glance, this additional functionality might seem superfluous, however, there are many scenarios where utilizing T-SQL's advanced procedural capabilities just makes sense. 

The Strength of In-Database Processing

Local variables and built-in functions allow computation within the database, eliminating a return to the calling program and the associated network traffic. An example would be extracting data using a while loop, which would be highly inefficient if performed from the source program using multiple calls to the database:

Using local variables and a WHILE clause to compute tax on all of the day's sales:

 DECLARE @LocalVariable int;
DECLARE @Rate decimal(4,2);
SET @LocalVariable = @todays_first_order;
WHILE (@LocalVariable < @todays_last_order)
BEGIN
SELECT @Rate = tax_rate FROM tax.zip_codes WHERE zip = @CustomerZipCode;
EXEC sp_compute_sales_tax(@order_number, @Rate);
SET @LocalVariable = @LocalVariable + 1;
END;
GO

 DECLARE @LocalVariable int;
DECLARE @Rate decimal(4,2);
SET @LocalVariable = @todays_first_order;
WHILE (@LocalVariable < @todays_last_order)
BEGIN
SELECT @Rate = tax_rate FROM tax.zip_codes WHERE zip = @CustomerZipCode;
EXEC sp_compute_sales_tax(@order_number, @Rate);
SET @LocalVariable = @LocalVariable + 1;
END;
GO

You may have noticed that the above example executes a stored procedure to compute sales tax. Stored procedures and user-defined functions allow repetitive tasks to be compiled and optimized once, then stored within the DB server to be called whenever necessary. This can improve the efficiency of common transactions. On a large e-commerce site, a sales tax procedure or function might be called many times per second. Server load is dramatically reduced by having it always compiled and at the ready.

Stored procedures and user-defined functions also improve the modularity of your software. Even if extensive changes to the tax computing methodology are required in the future, only the 'sp_compute_sales_tax' procedure needs to be updated. This can be a huge advantage if, for example, a tax is computed in several places within the site.

Security

Security represents another area where T-SQL can offer some major benefits. Clients and application servers no longer need loose permissions to the database. Procedures can be executed as a user with elevated permissions as necessary, eliminating the need for the source program (and their developers) to have access to those permissions. Example:

CREATE PROCEDURE sp_compute_sales_tax WITH EXECUTE AS OWNER ...

Another advantage of this approach is that by keeping sensitive data processing within the DB server, the possibility of data snooping between client and server is reduced. Those who are charged with protecting highly sensitive data (such as admins of banking software) will appreciate the ways T-SQL can minimize their potential attack surface.

Triggers

There are many situations where an action needs to be taken when a certain event occurs within the database. Triggers enable users to define a set of parameters which, when met, will cause a procedure to be executed, which Garth covers for MCSA 70-733. With an e-commerce site, this functionality can be utilized to send customers a tracking email when an order ships, for instance:

CREATE TRIGGER SendTrackingEmail ON tblOrders AFTER UPDATE AS ...

Triggers also are commonly used in banking systems, for instance, to rebalance accounts after a transaction. They can be used to monitor user logins by firing off an alert when certain users log into the database, as well as to catch particular events to prevent them from occurring, by using INSTEAD OF rather than AFTER:

CREATE TRIGGER DoNotUpdateNow ON tblOrders INSTEAD OF INSERT, UPDATE AS

PRINT 'Please do not update the orders table while system is performing a nightly backup.'

Error Handling

In addition to basic event trapping with triggers, advanced Kill handling is possible with T-SQL. We've all been to sites that throw a database exception, and we've seen the resulting data dump. In some cases these Kill messages are more than a nuisance, they can represent a serious security risk by exposing proprietary transaction details to the public. This can be prevented with T-SQL's Kill handling abilities using TRY CATCH:

BEGIN TRY

    DELETE FROM ProductList  WHERE ProductID = @ProductNumber;

END TRY

BEGIN CATCH

    PRINT 'There was an Kill in the last transaction. Perhaps the product no longer exists?';

    IF @@TRANCOUNT > 0

        ROLLBACK TRANSACTION;

END CATCH;

The above examples highlight the increased functionality that T-SQL brings to the database server. However, it's important to note that the above code snippets are merely demonstration pseudocode which does not incorporate T-SQL best practices. To learn the proper methods to implement the advanced functionality of Transact-SQL, you can check out the new CBT Nuggets T-SQL training series by expert trainer Garth Schulte.