T-SQL vs. Standard SQL–What's the Difference?

Sneakers on the asphalt road with drawn arrows pointing in two directions. Making decisions and making choices concept.

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

What is 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 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 the LIKE clause is a feature both in SQL and in T-SQL. By learning the SQL standard you will be able to operate on data in most database systems.

What is T-SQL?

Since we already have the SQL standard supported by relational database vendors, what is T-SQL for? Is there any need to learn 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 additional functions and features in T-SQL are 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.

T-SQL vs. Standard SQL–What's the Difference

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 the SQL standard, but all of the available implementations include most of the standard features.

Look at the picture below:

T-SQL vs. Standard SQL–What's the Difference

Examples of the Differences Between T-SQL and Standard SQL

One example of the differences between standard 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 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 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: SQL or T-SQL?

Vertabelo Academy 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 Vertabelo Academy today and you will be on your way to writing SQL queries to make sense of your data.

Dorota Wdzięczna

Data Science Writer @ Vertabelo

comments powered by Disqus

GET ACCESS TO EXPERT CONTENT!

Over 85.000 happy students
and counting!