Phillip Burton

If you are new to SQL, the differences between standard SQL and other variants of the SQL language, such as T-SQL, may confuse you. Very few proprietary SQL languages follow the SQL standard strictly. In this article, I will observe the differences between SQL and T-SQL, also written as T-SQL.

What are SQL and T-SQL?

SQL, Structured Query Language, was first created in the 1970s. In the 1980s, multiple companies started their own SQL programs. These versions varied from the original in different ways.

In the mid-1980s, two organizations, ISO and ANSI, published their SQL standards. This meant that others could use it as a starting point for their version. In this article, I will refer to the ISO/ANSI versions as “Standard SQL.”

70-461, 761: Querying Microsoft SQL Server with Transact-SQL

Last Updated May 2021

Bestseller
  • 277 lectures
  • Beginner Level
4.5 (7,211)

From Tables and SELECT queries to advanced SQL. SQL Server 2012, 2014, 2016, 2017, 2019, exams 70-461 and 70-761 | By Phillip Burton

Explore Course

Some of the major modern dialects of SQL are:

None of these dialects implement the ISO or ANSI SQL language in full. There are a few reasons:

So while some vendors get fairly close, there is no implementation of the full Standard SQL. Standard SQL is more of a concept rather than an actual implemented language. Similarly, there is no such thing in the real world as “English.” Instead, there are dialects, such as British English and American English. Some institutions may attempt to regulate their English dialect. However, dialects develop as actual usage varies due to changing circumstances. This is the same with Standard SQL, which evolves every few years.

Standard SQL does not replicate exactly the standard that its creator, Cobb, came up with. For example, while set theory uses records and fields, Standard SQL uses rows and columns. So even the SQL standard is not a perfect implementation of the original SQL theory.

So what are some of the differences between Standard SQL and MS SQL Server’s extension of SQL?

Referring to objects with a space in their name

Sometimes the names of tables and other objects include a space. You should consider this when writing SELECT statements:

SELECT * FROM My Table

In the above example, the SQL interpreter will not understand where the table name starts and ends. Instead, it will throw an error.

Standard SQL uses double quotation marks as these delimiters:

SELECT * FROM "My Table"

In T-SQL, you generally use square brackets.

SELECT * FROM [My Table]

Reducing the numbers of rows returned in a query

The results of a SELECT query could be hundreds of thousands or millions of rows long. You might want to limit it to the first (say) 5 or 10 rows.

In Standard SQL, you would use:

SELECT * FROM "My Table"
FETCH FIRST 10 ROWS ONLY

In T-SQL, there is a variant of this, first introduced in SQL Server 2012.

SELECT * FROM [My Table]
ORDER BY SomeField
OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY

However, the usual way of limiting the number of rows in T-SQL is by using TOP. This was first introduced in SQL Server in 2005:

SELECT TOP 100 * FROM [My Table]

You can use this with or without brackets:

SELECT TOP (100) * FROM [My Table]

The above is perfectly fine in T-SQL. Other SQL variants use different ways to restrict the number of rows, such as:

SELECT * FROM [My Table] LIMIT 100;

Do you need to use the FROM clause?

In Standard SQL, you must use at least the SELECT and FROM clauses in a SELECT statement. This means that you cannot easily retrieve the result of any calculation which does not require a table, such as 2+2.

Oracle SQL gets around this with a special built-in table called “dual,” which has one row and one column. So

SELECT 2+2 FROM dual;

will give you the answer 4.

In Microsoft’s SQL, you do not use the “dual” table. You can use:

SELECT 2+2

Procedural programming

Standard SQL queries data using set-based logic. For example, the totality of a table or view can be queried in one SELECT statement, retrieving millions of rows. You do not have to specify how SQL retrieves this data. In this way, Standard SQL is a declarative programming language. This is because you do not describe the control flow, but what result you want.

T-SQL includes extensions that allow you to use different ways of querying. For example:

BEGIN TRY
    	-- I make an error here.
END TRY
BEGIN CATCH
    	-- I deal with the error here.
END CATCH

These extensions make T-SQL an imperative as well as a procedural programming language. Imperative programming allows for the user to specify the commands to be undertaken. T-SQL is a procedural language because you can write code in procedures or functions.

Functions

T-SQL functions can differ from Standard SQL functions. For example, the SUBSTRING function in Standard SQL takes the form:

SUBSTRING(name_of_string FROM first_character FOR number_of_characters).

In T-SQL, you would use parameters separated by commas instead of a SQL statement:

SUBSTRING(name_of_string, first_character, number_of_characters)

Additionally, in T-SQL, you can add additional functions. The Standard SQL CAST function transforms one column or field from one data type to another. However, if you cannot convert it, it results in an error. In SQL Server 2012 onwards, Microsoft introduced the TRY_CAST function, which returned a NULL if there was an error. This makes for much easier programming.

Expanded DDL and DML statements

DDL, or data definition language, allows you to CREATE, ALTER and DROP objects such as tables and views. DML are data manipulation commands. Used in both Standard SQL and T-SQL, they are INSERT, UPDATE and DELETE.

T-SQL allows for expanded DDL and DML statements. In T-SQL, you can use JOINs in DML commands, allowing you to connect tables together while using these DML commands. Standard SQL would require you to use subqueries for this functionality.

Data types

SQL is a strongly typed language. This means that data in columns must be of a specified type, such as a string, text, or date-based type.

Standard SQL includes a wide selection of data types. T-SQL expands on this selection.

For example:

If the smalldatetime data type gives you all that you need, then you can save half the storage space by using it.

Conclusion

What conclusions can you reach? Firstly, SQL dialects are not identical. For example, if you used Oracle SQL, your SELECT statements would have to end with a semicolon. In T-SQL, it is generally optional. This means that SQL statements may need modification to go from one dialect to another.

However, as the different dialects stem from the same Standard SQL, over 90% of the code will work without modification. If you are modifying many SELECT statements, you will find that the adjustments become repetitive. One change will give you the basis for changing hundreds of SELECT statements.

Secondly, you can learn any of the SQL dialects with the knowledge that most of the syntax that you learn will be portable into other versions. For example, if you are trained in Oracle SQL and want to use T-SQL, some modifications will be necessary, but the underlying theory and most of the syntax remain the same.

How to learn T-SQL

I hope that you have enjoyed this article. If you would like to learn T-SQL, why not join me in my course “Querying Microsoft SQL Server?” In this course, we will go through T-SQL in detail so you can take your database SELECT statements to a whole new level.

Alternatively, if you want to learn Oracle SQL, please join me in my Oracle SQL course.

Thank you very much for reading this, and please have a look at my other blogs (see below). Keep learning!

Featured course

T-SQL Basics: Learn The Fundamentals

Last Updated January 2020

  • 1 total hour
  • 17 lectures
  • Beginner Level
5 (1)

Learning the Basics in 1 Single Course | By Pedro Planas

Explore Course

T-SQL students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.

Request a demo