Back to articles list March 19, 2019 - 7 minutes read 14 Differences Between Standard SQL and Transact-SQL Dorota Wdzięczna Dorota is an IT engineer and works as a Data Science Writer for Vertabelo. She has experience as a Java programmer, webmaster, teacher, lecturer, IT specialist, and coordinator of IT systems. In her free time, she loves working in the garden, taking photos of nature, especially macro photos of insects, and visiting beautiful locations in Poland. Tags: SQL basics learn sql MS SQL Server T-SQL In my last article, I roughly described how standard SQL differs from T-SQL and who should learn which. Now I'd like to focus on the syntax differences and illustrate these differences with examples. If you think T-SQL is an extension implementing all the features from standard SQL, you aren't right. However, in SQL Server you will find almost all the features of the SQL standard. In this article you will find examples of some of the differences in syntax between standard SQL and Transact-SQL. #1 Names of database objects In relational database systems we name tables, views, and columns, but sometimes we need to use the same name as a keyword or use special characters. In standard SQL you can place this kind of name in quotation marks (""), but in T-SQL you can also place it in brackets (). Look at these examples for the name of a table in T-SQL: CREATE TABLE dbo.test."first name" ( Id INT, Name VARCHAR(100)); CREATE TABLE dbo.test.[first name] ( Id INT, Name VARCHAR(100)); Only the first delimiter (the quotation marks) for the special name is also part of the SQL standard. What is different in a SELECT statement? #2 Returning values The SQL standard does not have a syntax for a query returning values or values coming from expressions without referring to any columns of a table, but MS SQL Server does allow for this type of expression. How? You can use a SELECT statement alone with an expression or with other values not coming from columns of the table. In T-SQL it looks like the example below: SELECT 12/6 ; In this expression we don't need a table to evaluate 12 divided by 6, therefore the FROM statement and the name of the table can be omitted. #3 Limiting records in a result set In the SQL standard you can limit the number of records in the results by using the syntax illustrated below: SELECT * FROM tab FETCH FIRST 10 ROWS ONLY T-SQL implements this syntax in a different way. The example below shows the MS SQL Server syntax: SELECT * FROM tab ORDER BY col1 DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY; As you notice, this uses an ORDER BY clause. Another ways to select rows, but without ORDER BY is by using the TOP clause in T-SQL: SELECT TOP 10 * FROM tab; #4 Automatically generating values The SQL standard enables you to create columns with automatically generated values. The syntax to do this is shown below: CREATE TABLE tab (id DECIMAL GENERATED ALWAYS AS IDENTITY); In T-SQL we can also automatically generate values, but in this way: CREATE TABLE tab (id INTEGER IDENTITY); #5 Math functions Several common mathematical functions are part of the SQL standard. One of these math functions is CEIL(x), which we don't find in T-SQL. Instead, T-SQL provides the following non-standard functions: SIGN(x), ROUND(x,[,d]) to round decimal value x to the number of decimal positions, TRUNC(x) for truncating to given number of decimal places, LOG(x) to return the natural logarithm for a value x, and RANDOM() to generate random numbers. The highest or lowest number in a list in the SQL standard is returned by MAX(list) and MIN(list) functions, but in Transact-SQL you use the GREATEST(list) and LEAST(list) functions. T-SQL function ROUND: SELECT ROUND(col) FROM tab; #6 Aggregate functions We find another syntax difference with the aggregate functions. The functions COUNT, SUM, and AVG all take an argument related to a count. T-SQL allows the use of DISTINCT before these argument values, so that rows are counted only if the values are different from other rows. The SQL standard doesn't allows for the use of DISTINCT in these functions. Standard SQL: SELECT COUNT(col) FROM tab; T-SQL: SELECT COUNT(col) FROM tab; SELECT COUNT(DISTINCT col) FROM tab; But in T-SQL we don't find a population covariance function: COVAR_POP(x,y), which is defined in the SQL standard. #7 Retrieving parts of dates and times Most relational database systems deliver many functions to operate on dates and times. In standard SQL, the EXTRACT(YEAR FROM x) function and similar functions to select parts of dates are different from the T-SQL functions like YEAR(x) or DATEPART(year, x). There is also a difference in getting the current date and time. Standard SQL allows you to get the current date with the CURRENT_DATE function, but in MS SQL Server there is not a similar function, so we have to use the GETDATE function as an argument in the CAST function to convert to a DATE data type. #8 Operating on strings Using functions to operate on strings is also different between the SQL standard and T-SQL. The main difference is found in removing trailing and leading spaces from a string. In standard SQL there is the TRIM function, but in T-SQL there are several related functions: TRIM(removing trailing and leading spaces), LTRIM(removing leading spaces), and RTRIM(removing trailing spaces). Another very often used string function is SUBSTRING. The standard SQL syntax for the SUBSTRING function looks like: SUBSTRING(str FROM start [FOR len]) but in T-SQL the syntax of this function looks like: SUBSTRING(str, start, length) There are reasons sometimes to add values coming from other columns and/or additional strings. Standard SQL enables the following syntax to do this: As you notice this syntax makes use of the || operator to add one string to another. But the equivalent operator in T-SQL is the plus sign character. Look at this example: SELECT col1 + col2 FROM tab; In SQL Server we also have the possibility to use the CONCAT function concatenates a list of strings: SELECT CONCAT(col1, str1, col2, ...) FROM tab; We can also repeat one character several times. Standard SQL defines the function REPEAT(str, n) to do this. Transact-SQL provides the REPLICATE function. For example: SELECT REPLICATE(str, x), where x indicates how many times to repeat the string or character. #9 Inequality operator During filtering records in a SELECT statement, sometimes we have to use an inequality operator. Standard SQL defines <> as this operator, while T-SQL allows for both the standard operator and the != operator: SELECT col3 FROM tab WHERE col1 != col2 #10 ISNULL function In T-SQL we have the ability to replace NULL values coming from a column using the ISNULL function. This is a function that is specific to T-SQL and is not in the SQL standard. SELECT ISNULL(col1) FROM tab; Which parts of DML syntax are different? In T-SQL the basic syntax of DELETE, UPDATE, and INSERT queries is the same as the SQL standard, but differences appear in more advanced queries. Let's look at them. #11 OUTPUT keyword The OUTPUT keyword occurs in DELETE, UPDATE, and INSERT statements. It is not defined in standard SQL. Using T-SQL we can see extra information returned by a query. It returns both old and new values in UPDATE or the values added using INSERT or deleted using DELETE. To see this information we have to use prefixes in INSERT, UPDATE, and DELETE. UPDATE tab SET col='new value' OUTPUT Deleted.col, Inserted.col We see the result of changing records with the previous and new values in an updated column. The SQL standard does not support this feature. #12 Syntax for INSERT INTO ... SELECT Another structure of an INSERT query is INSERT INTO … SELECT. T-SQL allows you to insert data from another table into a destination table. Look at this query: INSERT INTO tab SELECT col1,col2,... FROM tab_source It is not a standard feature but a feature characteristic of SQL Server. #13 FROM clause in DELETE and UPDATE SQL Server provides extended syntax of the UPDATE and DELETE with FROM clauses. You can use DELETE with FROM to use the rows from one table to remove corresponding rows in another table by referring to a primary key and a foreign key. Similarly, you can use UPDATE with FROM update rows from one table by referring to the rows of another table using common values (primary key in one table and foreign key in second, e.g. the same city name) Here is an example: DELETE FROM Book FROM Author WHERE Author.Id=Book.AuthorId AND Author.Name IS NULL; UPDATE Book SET Book.Price=Book.Price*0.2 FROM Author WHERE Book.AuthorId=Author.Id AND Author.Id=12; The SQL standard doesn't provide this syntax. #14 INSERT, UPDATE, and DELETE with JOIN You can also use INSERT, UPDATE, and DELETE using JOIN to connect to another table. An example of this is: DELETE ItemOrder FROM ItemOrder JOIN Item ON ItemOrder.ItemId=Item.Id WHERE YEAR(Item.DeliveredDate) <= 2017; This feature is not in the SQL standard. Summary This article does not cover all the issues about syntax differences between the SQL standard and T-SQL using the MS SQL Server system. However, this guide helps point out some basic features characteristic only of Transact-SQL and what SQL standard syntax isn't implemented by MS SQL Server. If you want to learn more about SQL Standard or T-SQL, click here. Tags: SQL basics learn sql MS SQL Server T-SQL You may also like The Complete Beginner’s Guide to SQL Fundamentals Find out what SQL and databases are, and how they work together. In this article, you'll find a complete guide to SQL fundamentals. Read more What's the Difference Between SQL and T-SQL? If you’re a SQL newbie and are confused by the difference between SQL and T-SQL, this article will help make things clear. Read more You Want to Learn SQL? You've Come to the Right Place! If you want to learn SQL basics or enhance your SQL skills, check out LearnSQL.com for a wide range of SQL courses and tracks. Read more Oracle Top-N Queries for Absolute Beginners Top-N queries retrieve a defined number of rows from a result set, i.e. 10 best-selling cars, 5 most popular routers, 20 worst-performing stores, etc. Read more Microsoft SQL Server 2017 Installation Step by Step I’ll show you all the SQL Server 2017 installation steps to help you install it on the Windows operating system together with SQL Management Studio. Read more Microsoft SQL Server Pros and Cons In this entry, we’ll go over the pros and cons of Microsoft SQL Server and evaluate the platform from the perspective of a company and a data specialist. Read more What Is Vertabelo’s SQL Cheat Sheet? Rock the SQL! You don’t have to be a programmer to master SQL. Download the SQL Cheat Sheet and find quick answers for the common problems with SQL queries. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.