Transact-SQL, the Microsoft and Sybase implementations of the Structured Query Language that is used by all databases, is well-known. What might not be apparent at first glance is how T-SQL adds a new set of capabilities to the database server. T-SQL is a fully procedural programming environment, which passes the Turing-completeness exam.
In this sense, T-SQL can be considered more than just a syntax for query execution. It combines data processing functionality with DML statements to modify, store, retrieve, or retrieve information within the database. In an earlier post, you can find more information about DML and DDL functions. Although this extra functionality may seem redundant at first, there are many situations where T-SQL’s advanced procedural capabilities can be useful.
The Strength of In-Database Process Processing
Built-in functions and local variables allow computation within the database without the need for a return to the calling programme and all associated network traffic. For example, a while loop extracts data from the source program. This would be extremely inefficient if it was done using multiple calls to a database.
Learn how to become a security expert with SPOTO’s Cybersecurity Training
Start training
DECLARE @LocalVariable int;DECLARE @Rate decimal(4,2);SET @LocalVariable = @todays_first_order;WHILE (@LocalVariable < @todays_last_order)BEGINSELECT @Rate = tax_rate FROM tax.zip_codes WHERE zip = @CustomerZipCode;EXEC sp_compute_sales_tax(@order_number, @Rate);SET @LocalVariable = @LocalVariable + 1;END;GO
The above example executes the stored procedure to calculate sales tax. Stored procedures and user defined functions allow repetitive tasks to been compiled and optimized once and then stored in the DB server to be called whenever needed. This can increase the efficiency of common transactions. A sales tax procedure or function may be called multiple times per second on large e-commerce sites. It is possible to reduce server load by having the code always available and compiled.
Your software's modularity is also improved by stored procedures and user-defined function. 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 great advantage, especially if a tax is calculated in multiple places on the site.
Security
T-SQL offers significant benefits in security. Clients and server applications no longer require database access rights. Procedures can now be executed by users with elevated permissions, eliminating the need to allow the source program (and its developers) access to the database. Example:
CREATE PROCEDURE sp_compute_sales_tax WITH EXECUTE AS OWNER ...
This approach also has the advantage of reducing the risk of data snooping between clients and servers by keeping sensitive data processing inside the DB server. Administrators of banking software, for example, will appreciate the ways T-SQL reduces their vulnerability to attack.
Triggers
There are many situations in which an action must be taken when a particular event occurs within the database. Triggers allow users to set a number of parameters that, when met, will trigger a procedure. Garth covers MCSA 70-733. This functionality can be used to send customers tracking emails when an order ships.
CREATE A TRIGGER SendTrackingEmail AFTER UPDATE AS
Bank systems also use triggers to rebalance accounts following a transaction. They can be used to monitor user log-ins by sending an alert to certain users when they log in to the database.