What's the Difference Between SQL and T-SQL?

If you are beginning to learn SQL and are confused by the differences between standard SQL and other similar languages like T-SQL, this article will help make things clear. You’ll not only learn about the difference between SQL and T-SQL but also find explanations concerning which topics would be better to start learning first: standard SQL or something more specific like MS SQL Server.

What is Standard SQL?

SQL (Structured Query Language) is a basic ANSI/ISO standard programming language designed to operate on data stored in relational databases. Thanks to these international standards the relational database systems that implement this language allow one to write similar queries across most systems. You can select data, manipulate data, create objects, manage users, and manage databases. Vendors of database management systems support most of the features of the ANSI/ISO SQL standards, however, companies also include their own non-standard features that extend the language. Learning the so-called standard SQL is fundamental to learning other extensions of the language like T-SQL, because these extensions implement most of the basic SQL features. For example, there’s no difference between SQL and T-SQL when it comes to LIKE clauses. By learning standardSQL you will be able to operate on data in most database systems.

What is T-SQL?

Since we already have standard SQL supported by relational database vendors, what is T-SQL for? Is there any need to learn T-SQL? What is the difference between SQL and T-SQL?

Even though almost all relational database systems use standard SQL, almost all of them also include non-standard extensions of the language. The specific implementation of SQL used in the Microsoft SQL Server database system is called the Transact-SQL language, or T-SQL for short. T-SQL has more features and more functions than what are specified in the SQL standard. SQL is almost a subset of T-SQL, so if you know T-SQL, you also know standard SQL, because it contains almost all of the features of standard SQL. The difference between SQL and T-SQL is that the latter has more features intended to help you in making query writing easier, quicker, and more efficient. So if you work in MS SQL Serve, you use T-SQL.

In other relational database systems the names of the extensions and the additional features are different. For example, Oracle has the PL/SQL language, and PostgreSQL implements PL/pgSQL.

Currently there is no implementation of SQL that includes 100% of the features of standard SQL, but all of the available implementations include most of the standard features.

Look at the picture below:

Examples of Differences Between T-SQL and Standard SQL

The first difference between SQL and T-SQL is the TOP keyword, which is used mainly in a SELECT statement. It indicates how many rows should be returned by a query in a result set. TOP is put after SELECT along with the number of rows to return. For example, the following query return only the first 12 results:

SELECT TOP 12 Id, Name, Description  
FROM Products ORDER BY Name;

You don't find this clause in standard SQL—it is available only in MS SQL Server. You can learn more about how and when to use the TOP statement in MS SQL Server in the course "SQL Basics in MS SQL Server".

Another difference between SQL and T-SQL is in the syntax of the SUBSTRING function used to cut a substring from a given string input. In standard SQL the syntax of this function is:

SUBSTRING(str FROM start [FOR len])

In the MS SQL Server the syntax of the function is:

SUBSTRING(str, start, len)

In these functions, str is a given string to search, start is a start number of a character (the first is 1) in a string from which the function will begin searching, and len is the length of the string to return.

These are only two examples of the differences between standard SQL and T-SQL, but you can learn more in this "SQL Basics in MS SQL Server" course and in this "SQL Basics" course.

Which is Better to Start Learning: Standard SQL or T-SQL?

LearnSQL.com offers two beginner courses: "SQL Basics" and "SQL Basics in MS SQL Server". You may be confused about which one to choose first. If you want to learn a language to communicate with any relational database, choose the “SQL Basics” course where you’ll learn standard SQL. The material you learn will be universal, giving you the power to use different relational database management systems (RDBMS). If, however, you want to obtain the knowledge about T-SQL in particular, because you plan on working with the MS SQL Server system, it is fine to start with the "SQL Basics in MS SQL Server" course to learn Transact-SQL in detail.

Summary

The choice of whether you start learning standard SQL, T-SQL, or any other variant, mostly depends on whether you know you will be using a particular database system. Start either one of these courses from LearnSQL.com today and you will be on your way to writing SQL queries to make sense of your data.