SQL vs T-SQL: Understanding the Differences
SQL or T-SQL — which one do you need to learn?
SQL and T-SQL both see heavy use in the database and data science industries. But what exactly are they? These two query languages are very similar, both in name and in what they can do, so the distinction between them can be difficult to understand.
In this post, we're going to: define what Standard SQL and T-SQL are, investigate the differences between them, provide examples of each, and summarize which you should be learning and why.
- Define what Standard SQL and T-SQL are
- Investigate the differences between them
- Provide examples of each
- Summarize which you should be learning and why
What is Standard SQL?
Standard SQL, usually referred to simply as "SQL," is a type of programming language called a query language. Query languages are used for communicating with a database.
SQL is used for adding, retrieving, or updating data stored in a database. It is used across many different types of databases. Meaning, if you learn the basics of SQL, you will be in a good position for a career in data.
Databases and the data stored within them are a core part of how many companies operate. An easy example is with a retailer that might store order or customer information in a database. SQL is a programming language that allows the company to work with that data.
What is T-SQL?
T-SQL, which stands for Transact-SQL and is sometimes referred to as TSQL, is an extension of the SQL language used primarily within Microsoft SQL Server. This means that it provides all the functionality of SQL but with some added extras.
You can think of it a bit like a SQL dialect — it's very similar to regular SQL, but it has a few extras and differences that make it unique.
Despite the clear and rigid specifications of standard SQL, it does allow for database companies to add their own extensions to set them apart from other products. T-SQL is an example of this for Microsoft SQL Server databases — T-SQL is central to the software and runs most operations within it.
Most major database vendors offer their own SQL language extensions for their own products, and T-SQL is one of the most widely-used examples of these (because Microsoft SQL server is popular).
Put simply: when you are writing queries within Microsoft SQL Server, you are effectively using T-SQL. All applications that communicate with SQL Server, regardless of the application's user interface, do so by sending T-SQL statements to the server.
However, in addition to SQL Server, other database management systems (DBMS) also support T-SQL. Another Microsoft product, Microsoft Azure SQL Database, supports most features of T-SQL.
T-SQL has been designed to make working with those databases that support it easier and more efficient.
What is the difference between SQL and T-SQL?
Now we have covered the basics of both, let's take a look at the main differences:
The obvious difference is in what they are designed for: SQL is a query language used for manipulating data stored in a database. T-SQL is also a query language, but it's an extension of SQL that is primarily used in Microsoft SQL Server databases and software.
SQL is open-source. T-SQL is developed and owned by Microsoft.
SQL statements are executed one at a time, also known as "non-procedural." T-SQL executes statements in a "procedural" way, meaning that the code will be processed as a block, logically and in a structured order.
There are advantages and disadvantages to each approach, but from a learner perspective, this difference isn't too important. You'll be able to get and work with the data you want in either language, it's just that the way you go about doing that will vary a bit depending on which language you're using and the specifics of your query.
On top of these more general differences, SQL and T-SQL also have some slightly different command key words. T-SQL also features functions that are not part of regular SQL.
An example of this is how in we select the top X number of rows. In standard SQL, we would use the LIMIT keyword. In T-SQL, we use the TOP keyword.
Both of these commands do the same thing, as we can see in the examples below. Both queries will return the top ten rows in the users table ordered by the age column.
SELECT * FROM users ORDER BY age LIMIT 10;
SELECT TOP 10 (*) FROM users ORDER BY age;
Finally, and as referenced before, T-SQL offers functionality that does not appear in regular SQL. One example is the ISNULL function. This will replace NULL values coming from a specific column. The below would return an age of “0” for any rows that have a value of NULL in the age column.
SELECT ISNULL(0, age) FROM users;
(There are ways of doing this in standard SQL too, of course, but the commands are slightly different.)
These are just a couple of code differences to give you an idea of how the two compare, but of course, there are many more. You can learn more about SQL commands with our extensive guide. And of course, Microsoft has documentation for working with T-SQL.
Which is better to learn?
If you want to work with databases in any way, or if you're seeking a data job, learning SQL is a necessity.
As T-SQL is an extension of SQL, you will need to learn the basics of SQL before starting. If you learn T-SQL first, you will end up picking up knowledge of standard SQL anyway.
With most things, which you choose to learn should depend on what you are trying to achieve. If you are going to be working with Microsoft SQL server, then it is worth learning more about T-SQL. If you are a beginner looking to get started in using databases, then begin with learning about SQL.
If you’d like to learn more about this topic, check out Dataquest's interactive Introduction to SQL and Databases course, and our SQL Fundamentals that help you master these skills in around 2 months.