Transact-SQL, often abbreviated as T-SQL, or TSQL, is a proprietary extension to SQL from Microsoft and Sybase. Transact-SQL extends the SQL standard to include additional features not included in the SQL standard.
The following is an example of a simple T-SQL statement:
CREATE DATABASE Movies;
This is a simple T- SQL example. This will create a new database called Movies.
However, T-SQL provides additional options for inclusion in this statement, such as where the database files should be located, the size of these files, what their maximum size should be, and so on.
Some features of T-SQL include:
1, Programming
2, Local variables
3, Support characters String processing, data processing, mathematics and other functions
4. Changes to DELETE and UPDATE statements. In particular, they allow adding a FROM clause, which allows inclusion of joins.
5. T-SQL has a BULK INSERT statement, which allows you to import data files into database tables or views in a user-specified format.
One of the main benefits of the above extensions is that you have more programmability when working with T-SQL and SQL. For example, stored procedures allow you to incorporate programming logic into the database. You can pass one or more parameters to a stored procedure, and the stored procedure will return an output that changes based on the value of the parameters.
T-SQL also contains a large number of built-in functions, as well as support for user-defined functions.
An example of a built-in function
Here is another basic example, this time using one of T-SQL’s built-in functions:
SELECT GETDATE();
Output:
2018-05-06 23:30:37.003
In this example, we use T-SQL’s GETDATE() function to return the current date and time. This is used with the SELECT statement, which is a common SQL statement that returns data.
We can also use this function as a parameter of another function. Here's an example:
SELECT FORMAT(GETDATE(), 'MMM');
Result:
May
History of T-SQL
While Transact-SQL (and SQL Server) It is best known as Microsoft, but it is actually the result of a collaboration between Microsoft and Sybase that began in 1987. This partnership was dissolved in 1994.
Since then, T-SQL has continued to be supported by database products released by both companies, but each company has developed its own 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 the core of SQL Server. Most operations in SQL Server are done using T-SQL. This is true even if you use a GUI tool like SSMS or DBeaver without actually doing any programming. Whenever you use an application to interact with SQL Server, the application uses T-SQL in the background to perform specified tasks.
But SQL Server is not the only database management system that supports Transact-SQL. Microsoft Azure SQL Database also supports T-SQL. Most T-SQL features used by applications are fully supported in Microsoft SQL Server and Azure SQL Database (although there are some exceptions).
About the GO command
Anyone who has used T-SQL is probably familiar with the GO command. This keyword is usually 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 of a Transact-SQL statement.
GO is one of the commands recognized by the sqlcmd and osql utilities and the SQL Server Management Studio code editor that facilitates readability and execution of batches and scripts. The GO command signals the end of a batch of Transact-SQL statements to a SQL Server utility.
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 consists of all statements entered since the last time the operation was performed, or, if this is the first time the operation was performed, since the beginning of the ad hoc session or script execution.
Other Proprietary SQL Extensions
Transact-SQL is not the only extension to the SQL standard. Various database systems have their own extensions. For example, Oracle uses a proprietary procedural language PL/SQL, and PostgreSQL uses a procedural language PL/pgSQL.
Although proprietary SQL extensions have their advantages (such as extended functionality), they also have disadvantages. One major disadvantage is that they can make things difficult if you need to port a database from one system to another. For example, if your organization suddenly decided to use Oracle instead of SQL Server, you might need to go through all of your SQL Server code and rewrite it for Oracle. This also requires you to be familiar with the syntax of both systems.
Related recommendations: "MySQL Tutorial"
The above is the detailed content of What is T-SQL?. For more information, please follow other related articles on the PHP Chinese website!