Daniel Calbimonte
Adventureworks SQL database

TSQL history

September 3, 2019 by

In this article, we’ll take a look at a brief TSQL history and a few examples of loops, conditionals, and stored procedures

Introduction

TSQL also named T-SQL or Transact-SQL is an SQL extension used in SQL Server created and used by Sybase to write queries. In this article, we will talk about the Transact-SQL history including some SQL history to understand the evolution.

Requirements

  1. SQL Server installed and SSMS to run queries and code
  2. The Adventureworks database installed

Adventureworks SQL database

SQL history

To understand T-SQL, you need to study SQL first. The SQL which was initially SEQUEL (Structured English Query Language). This language was initially developed by IBM in San Jose, California. The name SEQUEL was changed to SQL later due to some trademark problems with a UK company, but now SEQUEL is the official pronunciation of SQL

SQL now stands for Structured Query Language and it is a standard used by the databases to query Structured Languages. Even several NoSQL databases use similar languages.

There were several revisions of the SQL. The first revision was on 1986 (SQL-86) the second one on 1989 (SQL-89) and the main revision was SQL 92 (SQL-92). After that major revision, there were other minor revisions:

  • SQL 99 (included some OLAP concepts, Boolean data types, role-based access)
  • SQL 2003 (included some XML features, windows functions, more OLAP features, and other features)
  • SQL 2006 (includes more features related to Storing XML, XQuery, etc.)
  • SQL 2008 (includes definitions for TRUNCATE statements, FETCH Clauses, INSTEAD OF)
  • SQL 2011 (includes revisions for temporal data, some additional definitions for Windows Functions and also the FETCH clause
  • SQL 2016 includes features related to JSON data, polymorphic table functions, and row pattern matching
  • SQL 2019 is related to the multidimensional arrays

TSQL history

T-SQL is based on SQL but extended. T-SQL means Transaction SQL and it was originally used by Sybase (a database than was popular in the past) and then after a while used by Microsoft SQL Server. If you did not know, SQL Server was based in Sybase technology at the beginning. For more information about SQL Server history, refer to this article:

TSQL basis

In this article, we will not teach SQL which is a database query language used by the databases. We will focus on Transact-SQL that includes some functionality not necessarily included in SQL.

You can use Transact-SQL to solve some programming problems not related to databases. What I am trying to say is that you can write code and solve problems not related to databases.

Requirements

In order to start, you will need SQL Server installed and SQL Server Management Studio (SSMS). You could use other tools to write the code, but this is the official tool.

For queries, we will use the Adventureworks database.

TSQL samples

Let’s start with some code:

The first example is the famous Hello world. To do this we can use the following code:

You could also use the PRINT command:

The select prints as a column the result of Hello world, whereas the PRINT, prints as a message.

TSQL variables

You can use variables. The following example will show how to select characters, numbers, and dates:

For more information about data types, refer to this article:

The next example shows how to use a variable in a query. We will use the variable in a query. We will store the value 3 in the variable departmentid and show the department id and name from the table humanresource.department where department id is equal to 3:

TSQL LOOPS

In this programming language, you do not have a loop sentence like in C++, C#, and Java. You use the WHILE Sentence instead.

The following example will create a table named myIDs and insert 10 values using a WHILE sentence:

TSQL if conditionals

The use of conditionals uses the IF sentences. The use of IF and ELSE is similar to other programming languages, the following examples show the use of IF and ELSE.

The following example detects how old is a person with the date. The function GETDATE gets the current date and the variable myBirthDay store the birthday date. The function DATEDIFF gets the number of years between the current date and the birthday. Finally, the if sentence classifies as adult or children according to the age calculated. If the age is higher than 18, the person is an adult. Otherwise, the person is a child.

TSQL Stored Procedure

SQL Server Stored Procedures

Using Stored Procedures is a best practice and it is highly recommended in SQL Server. If possible, always use stored procedures instead of a raw T-SQL query for security and performance.

The following example will show how to use a stored procedure with a parameter. In this stored procedure, we have a stored procedure named CountryName and we pass as a parameter the Country Region Code. We do a select of two columns of the table Person.CountryRegion from the Adventureworks database where the region code is specified as a parameter:

If we want to invoke the stored procedure, the following code should be used:

We are calling the stored procedure CountryName and we are sending the Region Code.

Conclusion

In this article, we learned about SQL and Transact-SQL. We first learn the history first and then we jump into basic code. We learned loops, conditionals, and stored procedures.

Daniel Calbimonte

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte
Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

T-SQL

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.He also helps with translating SQLShack articles to SpanishView all posts by Daniel Calbimonte

362 Views