Relational databases are one of the most preferred and in-demand database systems for database developers and Structured Query Language (SQL) plays an important role in most database-driven applications.
SQL is an ANSI/ISO standard language used for performing data operations on relational databases and relational database management systems (RDBMS). These international standards enable the user to be able to use similar queries across different database systems.
If you are new to database programming or database administration, you may have some confusion about how to choose the right language for learning how to develop database-driven apps. SQL and T-SQL (another popular database programming language) are often confused. To help clear this up, this database programming tutorial will discuss the subtle differences between SQL and T-SQL and shed some light on how to choose the right language for database development.
Read: Learn the Basics of Extracting Data in SQL
What is a Relational Database?
A relational database is used to work on databases whose tables are related to one another. In a relational database, each record is called a row, which we can identify through a unique key.
The ISO/ANSI standard has laid down some set of rules and conventions to work with relational databases. These rules are enclosed in a single language called Structured Query Language (SQL).
Some of the use cases for relational databases include use in e-commerce applications, inventory control systems, processing business transactions, holding data of entities such as customers, suppliers, and so forth.
Read: Best Relational Database Systems (RDBMS)
What is SQL?
SQL is a basic ANSI/ISO query language used for operating on data in a relational database. With SQL, not only you will be able to manipulate data in a relational database environment, but it also allows database programmers to join tables by using JOINS.
Learning SQL will build a solid foundation for programmers to learn other query languages because most of the non-standard extensions of SQL languages contain most of the features of SQL. For example, a LIKE clause works the same way in SQL and T-SQL.
Some common SQL-based languages include:
- DML (Data Manipulation Language) – Perform operations like insert, update and delete database records.
- DDL (Data Definition Language) – Perform operations like create, alter, truncate or drop table.
- DCL (Data Control Language) – Perform operations to control the database by granting and revoking the data.
- TCL (Transactional Control Language) – Perform operations like committing database or rolling back to a specific checkpoint.
By learning SQL, you will be able to work on most of the popular database systems.
Now that you know what SQL is, let’s get familiar with T-SQL.
What is T-SQL?
You may be wondering why we need T-SQL if we already have the standard-bearer SQL, which is used by the majority of relational database vendors. Is there any need to learn T-SQL if we already know SQL?
Even though most of the relational database vendors use standard SQL, there are many database systems that use a non-standard extension of SQL too. Microsoft SQL Server database system, for example, uses a specific implementation of SQL called Transactional SQL – or in short, T-SQL. T-SQL comprises all features and functions of SQL with add-on features including transactional control, exceptional handling, making use of variables, and record processing.
In some ways, you can say SQL is a subset of T-SQL. This means if you know T-SQL, you know SQL too. Note that, while working with the Microsoft SQL Server database system, the application interacting with the server uses T-SQL statements under the hood.
Example of SQL Server
Difference Between SQL and T-SQL
Below are the key differences between Standard SQL and T-SQL
- SQL is an open-source query language while T-SQL is owned and maintained by Microsoft
- SQL is used for DML and DDL commands. But in the case of T-SQL, you can use triggers, views, and functions which all are called T-SQL objects
- SQL is a data-oriented language while T-SQL is transactional oriented and with T-SQL you can add business logic to the application from the backend
- T-SQL comprises all features of SQL while the reverse is not possible
- T-SQL uses some keywords that are very slightly different than that of SQL. It also uses some commands or functions that are not present in standard SQL
- SQL follows a non-procedural approach while T-SQL follows a procedural approach. This means SQL statements are executed at one time while T-SQL statements are executed sequentially. The code is processed as blocks in T-SQL.
Below are some examples to help distinguish the differences between SQL and T-SQL.
To start, one of the differences between SQL and T-SQL is the TOP keyword. TOP is used to fetch a particular number of rows in a result set. TOP is used with the SELECT statement, along with the number of rows to return. The following code snippet illustrates this:
SELECT TOP 10 CustomerId, CustomerName, Address FROM Customers ORDER BY CustomerName;
You can find the above command in MS SQL Server only – it is not available in standard SQL.
Let’s take another case of the Substring function. The Substring function is used to cut a substring from a given string. The syntax of Substring is different in SQL and T-SQL as described below.
The function in standard SQL is defined as:
SUBSTRING(string FROM start [FOR length])
In MS SQL SERVER, the syntax is as follows:
SUBSTRING(string, start, length)
SQL versus T-SQL
A database programmer who has just started their journey in the database world may find it difficult to choose the language they should learn. If you want to learn a language that can give you the power to use the material that you have learned universally – and that can be used in almost every relational database management system – then you should go for SQL.
If, however, you already have a plan to work on a specific database system, such as Microsoft SQL Server, then learning T-SQL will be an asset.
Furthermore, you may also want to learn other languages. For example, PL/SQL, which is an Oracle extension to standard SQL, and PL/pgSQL, which is a PostgreSQL extension to SQL.
Final Thoughts on SQL and T-SQL
Deciding which database programming language you may want to learn becomes easier if you know exactly which database systems you are going to use. Most database beginners do not immediately understand the difference between SQL and T-SQL and might confuse them by treating them as entirely different languages. This database administration tutorial is written especially for beginner database programmers to help clear up the differences between the two options.
Read more database programming and database administration tutorials.
