T-SQL (Transact-SQL)
T-SQL (Transact-SQL) is a set of programming extensions from Sybase and Microsoft that add several features to the Structured Query Language (SQL), including transaction control, exception and error handling, row processing and declared variables.
All applications that communicate with SQL Server do so by sending T-SQL statements to the server. T-SQL queries include the SELECT statement, selecting columns, labeling output columns, restricting rows and modifying a search condition.
T-SQL identifiers, meanwhile, are used in all databases, servers, and database objects in SQL Server. These include tables, constraints, stored procedures, views, columns and data types. T-SQL identifiers must each have a unique name, are assigned when an object is created and are used to identify an object.
T-SQL statement examples
The most popular T-SQL statement is the stored procedure, which is a compiled and stored T-SQL code. Similar to views, stored procedures generate an execution plan when called the first time. The difference is stored procedures can select data and execute any T-SQL code within any parameters.
User-defined functions are another example of T-SQL statements. User-defined functions take input parameters, perform an action and return the results to the call.
Another example is a trigger, which is a stored T-SQL script that runs when a statement other than SELECT is issued against a table or view. The two common triggers are AFTER triggers and INSTEAD OF triggers.
Programming T-SQL statements enables IT pros to build applications contained within SQL Server. These applications — or objects — can insert, update, delete or read data stored in a database.
Common language runtime (CLR) integration is the final T-SQL statement example. Since SQL Server 2005, IT pros can integrate with the .NET Framework CLR. This enables you to use .NET programming languages within SQL Server objects to create stored procedures, user-defined functions and triggers.
T-SQL Functions
In addition to SQL Server’s built-in functions, users can define functions using T-SQL.
Types of T-SQL functions include aggregate functions, which operate on a collection of values, but return one summary value; ranking functions, which return a ranking value for every row within a partition; rowset functions, which return an object that can be used as a table reference in SQL statements; and scalar functions, which operate on a single value and return a single value.
SQL Server also supports analytical functions in T-SQL to depict complex analytical tasks. These analytical functions enable IT pros to perform common analysis, such as ranking, percentiles, moving averages and cumulative sums to be expressed in a single SQL statement.