What is T-SQL?

Posted on by Ian

Transact-SQL, often abbreviated to T-SQL or even TSQL, is Microsoft’s and Sybase’s proprietary extension to SQL. Transact-SQL expands on the SQL standard to include extra features that aren’t included in the SQL standard.

Here’s an example of a simple T-SQL statement:

CREATE DATABASE Movies;

This is as simple as a T-SQL example could get. This creates a new database called Movies.

However, T-SQL provides for other options to be included in this statement, such as where the database files should be located, the size of those files, what their maximum size should be, and more.

T-SQL Features

Some of the features of T-SQL include:

  • Procedural programming
  • Local variables
  • Various support functions for string processing, date processing, mathematics, etc
  • Changes to the DELETE and UPDATE statements. In particular, they allow a FROM clause to be added, which allows joins to be included.
  • T-SQL has a BULK INSERT statement that allows you to import a data file into a database table or view in a user-specified format.

One of the main benefits of the above extensions is that you have much more programmability when using T-SQL vs SQL. Stored procedures for example allow you to incorporate programming logic into your database.  You can pass one or more parameters to a stored procedure, and the stored procedure will return an output which varies depending on the values of the parameters.

T-SQL also includes a large number of built-in functions, as well as supporting user-defined functions.

An Example of a Built-In Function

Here’s another basic example , this time using one of T-SQL’s built-in functions:

SELECT GETDATE();

Result:

2018-05-06 23:30:37.003

In this case we use T-SQL’s GETDATE() function to return the current date and time. This is used along with the SELECT statement, which is a common SQL statement for returning data.

We can also use that function as an argument for another function. Here’s an example:

SELECT FORMAT(GETDATE(), 'MMM');

Result:

May

In this case we use the T-SQL FORMAT() function to extract just the month portion of the date, and present it in a certain format. You can see more date/time examples at How to Format the Date & Time in SQL Server.

History of T-SQL

Although Transact-SQL (and SQL Server) is most commonly known as a Microsoft thing, it’s actually the result of a partnership between Microsoft and Sybase that started in 1987. This partnership was dissolved in 1994.

Following this, T-SQL continued to be supported by database products released by both companies, however, each company developed its database products in its own direction. Therefore, there are some differences between Sybase T-SQL and Microsoft T-SQL.

T-SQL and GUI Tools

Transact-SQL is central to SQL Server. Most operations in SQL Server are done using T-SQL. This is true even if you use a GUI tool (such as SSMS or DBeaver) and you don’t actually do any programming. Any time you use an application to interact with SQL Server, the application uses T-SQL behind the scenes to carry out the specified task.

But SQL Server isn’t the only database management system that supports Transact-SQL. Microsoft Azure SQL Database also supports T-SQL. Most T-SQL features that applications use are fully supported in both Microsoft SQL Server and Azure SQL Database (although there are some exceptions).

About the GO Command

Anyone who has used T-SQL will probably be familiar with the GO command. This keyword is often placed at the end of a batch of statements. For example, the first example above could also be written like this:

CREATE DATABASE Movies;
GO

However, the GO command is not actually part of Transact-SQL. In fact, the GO command cannot even occupy the same line as a Transact-SQL statement.

GO is one of the commands that are recognized by the sqlcmd and osql utilities, as well as SQL Server Management Studio Code Editor to facilitate the readability and execution of batches and scripts. The GO command signals the end of a batch of Transact-SQL statements to the SQL Server utilities.

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

Other Proprietary SQL Extensions

Transact-SQL isn’t the only extension to the SQL standard. Various database systems have their own extensions. For example, Oracle uses a proprietary procedural language called PL/SQL, and PostgreSQL uses a procedural language called PL/pgSQL.

Although proprietary SQL extensions have their advantages (such as extended functionality), they do have their disadvantages. One major disadvantage is that they can make things difficult if you ever need to port a database from one system to another. For example, if your organization suddenly decides that it’s going to use Oracle instead of SQL Server, you may need to go through all the SQL Server code and rewrite it for Oracle. This also requires you to come up to speed with the syntax of both systems.