14 Differences Between Standard SQL and Transact-SQL

by Dorota Wdzięczna 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.

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

by Dorota Wdzięczna 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.

How to Group by Year in T-SQL

Problem: You want to group your data by year. Example I: One of the columns in your data is transaction_date. It contains a date. You would like to group all your data by year and calculate the total money earned each year. The data table looks like this: transaction_datemoney 2018-03-251700 2019-09-12100 2018-07-141200 2018-01-05400 2019-06-082000 2020-03-061500 Solution 1 (displaying the year and the money earned): SELECT YEAR(transaction_date) AS year, SUM(money) AS money_earned FROM data GROUP BY YEAR(transaction_date); The result is:

How to Group by Two Columns in SQL

Problem: You want to group your data by two columns so you can count some statistics. Example: In the order table, you have the columns order_date, product_id, customer_id, and number. You would like to count the number of products bought by each customer each day. The order table looks like this: order_dateproduct_idcustomer_idnumber 2020-11-25711 2020-11-251213 2020-11-265312 2020-11-26124 2020-11-26321 2020-11-261627 2020-11-26332 2020-11-27631 Solution: SELECT order_date, customer_id, SUM(number) AS products_number FROM order The result is:

How to Calculate the Difference Between Two Datetimes in T-SQL

Problem: You have two columns of the type datetime and you want to calculate the difference between them. Example: In the travel table, there are three columns: id, departure, and arrival. You'd like to calculate the difference between the arrival and the departure. The travel table looks like this: iddeparturearrival 12018-03-25 12:00:002018-04-05 07:30:00 22019-09-12 15:50:002019-10-23 10:30:30 32018-07-14 16:15:002018-07-14 20:40:30 42018-01-05 08:35:002019-01-08 14:00:00 Solution 1 (difference in seconds): SELECT id, departure, arrival, DATEDIFF(second, departure, arrival) AS difference FROM travel; The result is:

How to Calculate the Difference Between Two Dates in T-SQL

Problem: You have two columns of the date type and you want to calculate the difference between them. Example: In the travel table, there are three columns: id, departure, and arrival. You'd like to calculate the difference between arrival and departure, or the number of days from arrival to departure inclusively. The travel table looks like this: iddeparturearrival 12018-03-252018-04-05 22019-09-122019-09-23 32018-07-142018-07-14 42018-01-052018-01-08 Solution: SELECT id, departure, arrival, DATEDIFF(day, departure, arrival) AS date_difference, DATEDIFF(day, departure, arrival) + 1 AS days_inclusive FROM travel; The result is: