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 the following 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.
- 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.
Difference between T-SQL and SQL
There are three distinct differences between the two.
- While T-SQL is an extension to SQL, SQL is a programming language.
- T-SQL contains procedural programming and local variable, while SQL does not.
- T-SQL is proprietary, while SQL is an open format.
Joins in T-SQL
Joins in T-SQL are clauses used to combine rows from two or more tables, based on a related column between them. Joins specify how SQL should use data from one table to select the rows in another table. Several operators -- such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT -- can be used to join tables.
Different types of joins are available in T-SQL. They include, for example, inner joins and outer joins. An inner join, which returns rows when there is a match in both tables, can be specified in either the FROM or WHERE clauses. Outer joins, which can be specified in the FROM clause only, finds and returns matching data and some dissimilar data from tables.
