Introduction to Transact SQL (T-SQL)

SQL is a common language used in the database world. Most modern RDBMS products use some sort of SQL dialect as their primary query language. SQL can be used to create or destroy objects such as tables on a database server and to manipulate those objects, such as adding, editing, deleting data to them, or retrieving data from they.

Transact-SQL (T-SQL) is a version of SQL inherited and developed by Microsoft, called T-SQL, this language is implemented to implement a standardized way of communicating with databases.

Transact-SQL is a powerful language that provides features such as data types, temporary objects, and extended stored procedures. Scrollable cursors, conditional handling, transaction control, exceptions, and error handling are also supported by Transact-SQL.

T-SQL in SQL Server 2019 improves performance over previous versions, increasing features and supporting many advanced features. Improvements include scalar functions, pagination, sequencing, metadata allocation, and better error handling support…

The examples below all use AdventureWorks2019, install AdventureWorks2019 on the machine to test the examples.

Consider the Transact-SQL statement below, which is a SELECT statement, used to retrieve the data of the loginID column of the employee table with JobTitle as ‘Design Engineer’ in the Employee table:

USE AdventureWorks2019 SELECT LoginID FROM HumanResources.Employee WHERE JobTitle = 'Design Engineer'

T-SQL includes many syntactic elements used by or affecting most statements. These elements include data types, predicates, function variables, expressions, flow control, comments, and batch seperators.

List of T-SQL statement types

SQL Server supports 3 types of T-SQL statements: DDL, DML, and DCL

Data Definition Language (DDL)

DDL, which is part of the RDBMS, is used to define and manage all the properties of the data, including row layout, column definitions, primary key columns, file locations, and storage. The DDL commands are used to construct and modify the structure of tables and objects such as views triggers, store procedures… For each object, there are keywords CREATE , ALTER , DROP . Example: CREATE TABLE, ALTER TABLE, DROP TABLE

Most DDL statements conform to a standard, where object_name is the name of the table, view, trigger, stored procedure… as follows:

• CREATE object_name
• ALTER object_name
• DROP object_name

Data Manipulation Language (DML)

DML is used to select (retrieve), insert (add), update (update) or delete (delete) data in objects (objects) defined by DDL. All users in the database can use these statements in operations on the database.

DML statements that include the following keywords:

• SELECT
• INSERT
• UPDATE
• DELETE

Data Control Language (DCL)

Data is an important part of the database, so appropriate steps should be taken to check that no invalid users have access to the data. A data control language is used to control permissions on database objects. Permissions are controlled using GRANT, REVOKE, DENY statements. DCL statements are also used to secure the database. The three basic DCL statements are as follows:

• GRANT (assign)
• REVOKE (recall)
• DENY (deny)

Data types

Data type is an attribute that defines the type of data or objects that can be contained. Data types must be provided for columns, parameters, variables, function return data values, and sctored procedures with return. T-SQL includes a number of data types such as varchar,text,int … All data stored in SQL Server must be compatible with one of the basic data types.

Objects have data types:

• Columns (columns) represent in tables and views
• parameters in stored procedures
• Variables (variables)
• function that returns one or more values with a specified data type
• Stored procedures that return code of type integer

SQL Server supports 3 types of data types as follows:

System-defined data types

This data type is provided by SQL Server, shown in the following table:

Category	Data type	Column of this data type
Exact Numberics (exact numbers/natural numbers)	int	Occupies 4 bytes of memory, used to store integer values, can store values from -2^31(-2,147,483,648) to 2^31-1 (2,147,483,647)
	smallint	Occupies 2 bytes of memory, can store integer values from -32.768 to 32m.767
	tinyint	Occupies 1 byte of memory, contains values from 0 to 255
	bigint	Takes up 8 bytes of memory. Holds data from -2^63 to 2^63-1
	numberic	has a fixed precision and scale
	money	takes up 8 bytes of memory space. Represents currency data values between -2^63/1000 and 2^63-1
Approximate Numbers (approximate/real numbers)	float	Takes up 8 bytes of memory. Number representation after floating point from -1.79E+308 to 1.79E+38
	real	Occupies 4 bytes of memory. Represents numbers after floating point from -3.40E+38 to 3.40E+38
Date and Time	datetime	Represents date time, occupies 8 bytes in memory (2 times 4 bytes – integer)
	smalldatetime	Date time performance
Character String	char	Store character data with specified length and non-Unicode
	varchar	Stores variable-length and non-Unicode character data up to 8,000 characters
	text	Stores variable-length and non-Unicode character data with a maximum length of 2^31 – 1(2,147,483,647) characters
	nchar	Stores Unicode characters with a specified length
	nvarchar	Stores variable unicode character data.
Other Data Types	timestamp	Takes up 8 bytes of memory. Stored values can be automatically generated, unique binary numeric values generated and stored in a real-time unix timestamp simulation database.
	binary(n)	Stores binary data of a specified length, up to 8000 bytes.
	varbinary(n)	Stores binary data with variable length, up to 8000 bytes.
	image	Stores a variable binary value with a maximum length of 2^30-1 (1,073,741,823) bytes.
	uniqueidentifier	Occupying 16bytes of memory, will automatically generate a unique value that is globally unique identifier (GUID)

Alias data types (alias data types)

These data types are based on the data types provided by the system. An alias data type is used when multiple tables store the same data type in a column and have similar characteristics such as length, nullability, and data type. In such cases, an alias data type can be created that can be used universally by all these tables.

Alias data types can be created via the CREATE TYPE statement. The syntax of the CREATE TYPE statement is as follows:

CREATE TYPE [schema_name.]type_name FROM base_type [NULL/NOTNULL];

For example:

CREATE TYPE usertype from varchar(20) NOT NULL

User-defined types

Users can define their own data types using a programming language supported by the .NET Framework.

Transact-SQL Language Elements

Transact-SQL language elements are used in SQL Server 2019 to work on imported data in SQL Server databases. The Transact-SQL language elements include predicates, operators, variables, functions, expressions, control of flow, and errors. error) and transactions, comments and batch separators.

Predicates (predicate)

Predicates can be understood as a logical expression used to evaluate whether the expression returns TRUE, FALSE or UNKNOWN.

The clauses in Transact-SQl are used in the following cases:

• Determines whether a specified value matches any value in a subquery or a list
• Specify a range of values to check
• Used to match characters with a specified pattern
• Search for exact or less exact matches with single words and phrases, words within a certain distance from each other, or weighted matches

example predicates clauses:

Predicate	For example
PRINT	SELECT PersonType, Title, FirstName,LastName FROM AdventureWorks2019.Person.Person WHERE PersonType IN (‘EM’,’SC’)
BETWEEN	SELECT BusinessEntityID, NationalIDNumber, LoginID, JobTitle, HireDate FROM AdventureWorks2019.HumanResources.Employee WHERE HireDate BETWEEN ’01-01-2010′ AND ’01-01-2013′
LIKE	SELECT DepartmentID, Name, GroupName, ModifiedDate FROM AdventureWorks2019.HumanResources.Department WHERE Name LIKE ‘P%’
CONTAINS	SELECT * FROM AdventureWorks2019.Person.Address WHERE CONTAINS (AddressLine1,’Street’)

Operators (operators)

Operators are used to perform arithmetic, comparison, concatenation, or assignment of values. For example, the data can be checked to verify that the COUNTRY column for customer data is populated (or has a NOT NULL value). In queries, anyone who can see the data in the table that requires the operator can perform operations. The appropriate permissions are required before the data can be successfully changed. SQL Server has seven types of operators:

Operator	description	Example
Comparison	Compares a value with another value an expression	=,<,>,>=,<=,!=,!>
Logical	Checking the true (logical) result of a condition	AND, OR, NOT
Arithmetic	Perform arithmetic operations such as addition, subtraction, multiplication and division	+,-,*,/,%
Concatenation	Combine 2 chains into one string	+
Assignment	Assign a value to a variable	=

The order of precedence of operators:

Order	Operator
first	() Parentheses
2	*, / , %
3	+ , –
4	= , < , > , >= , <= , != , !>
5	NOT
6	AND
7	BETWEEN, IN , CONTAINS, LIKE, OR
8	=

Example of operator precedence:

DECLARE @Number int; SET @Number = 2 + 2 * (4 + (5 - 3)) SELECT @Number

The result will display 14, with the order of execution as follows:

1. 2 + 2*(4+(5-3)) 2. 2 + 2*(4+2) 3. 2 + 2*6 4. 2 + 12 5. 14

Functions (functions)

A function is a set of statements, a T-SQL statement consists of a set of functions that are very useful for calculating or working with data. In SQL, the function works with data, groups the data, to return the required value, and then can use the SELECT clause to get the data returned from the expression

There are 4 types of functions in SQL Server as follows:

Rowset functions

in transact-SQL, the rowet function is used to return an object that can be used in place of a table reference. For example, OPENDATASOURCE, OPENQUERY, OPENROWSET, and OPENXML are rowset functions.

Aggregate functions

T-SQL provides a set of functions to support summarizing large volumes of data e.g. SUM, MIN, MAX, AVG, COUNT, COUNTBIG…

Ranking functions

Processing many tasks, such as creating arrays, generating ordinal numbers, finding ranks, etc. can be done more easily and faster by using the ranking functions. For example, RANK,DENSE_RANK,NTILE, ROW_NUMBER are ranking functions.

Scalar functions

In scalar functions, the input is a single value and the output is also a single value

Some scalar functions in SQL:

Function type	Description	Example _
Conversion function	The conversion function is used to convert the value of one data type to another. In addition, it can be used to get special date formats.	CONVERT
Date and time function	The datetime function is used to work with date times data, useful for calculating time	GETDATE, SYSDATETIME, GETUTCDATE, DATEADD, DATEDIFF, YEAR, MONTH, DAY
Mathematical functions	Math functions perform algebraic operations on numeric values.	RAND, ROUND, POWER, ABS, CELLING, FLOOR
System functions	SQL Server provides system functions that return metadata or configuration settings	HOST_ID,HOST_NAME,ISNULL
String functions	String functions are used to handle input such as char or nvarchar. Output can be string or numeric value	SUBSTRING, LEFT, RIGHT, LEN, DATALENGTH, REPLACE, REPLICATE, UPPER, LOWER, RTRIM, LTRIM

There are also some other scalar functions in SQL Server like cursor functions, logical functions, metadata functions, security functions…

Variables (variables)

A variable is an object that can store data values. In T-SQL, variables can be divided into local variables and global variables.

In T-SQL local variables are created and used for temporary storage when SQL statements are executed. Data can be passed through SQL statements via local variables. The name of a local variable is always prefixed with the keyword ‘@’.

For example:

DECLARE @Search NVARCHAR(30) SET @Search = N'hello'

In previous versions of SQL Server, a concept called global variables existed, which refers to built-in variables that are defined and maintained by the system. In SQL Server 2019, substitutions are classified as functions. They are prefixed with two ‘@’ signs. The return values of these functions can be retrieved with a simple SELECT query.

For example

SELECT @@LANGUAGE as 'Language'

They will return the language used by SQL Server

Some of the functions that can be encountered:

• @@DATEFIRST
• @@LANGUAGE
• @@LOCK_TIMEOUT
• @@MAX_CONNECTIONS
• @@SERVERNAME
• @@VERSION

Expression (expression)

An expression is a combination of identifiers, values, and operators that SQL Server can evaluate to get a result. Expressions can be used in a number of different places when accessing or changing data.

Example of an expression combining the SELECT clause, getting the current year and calculating the next year

USE AdventureWorks2019 SELECT SalesOrderID, CustomerID, SalesPersonID, TerritoryID, YEAR(OrderDate) AS CurrentYear, YEAR(OrderDate) + 1 AS NextYear FROM Sales.SalesOrderHeader

Control of Flow, Errors, Transactions

Although transact-SQL is primarily a data access language, it supports control of flow for execution and error detection. Control of flow defines the flow of execution. of transact-SQL statements, blocks of codes, user-defined functions, and store procedures.

Common controls of flow in T-SQL

Control-of-Flow Statement	description
IF … ELSE	Branching control based on logical condition
WHILE	Repeat commands or blocks of statements while the test condition is true
BEGIN … END	Scope definition of a T-SQL block
TRY … CATCH	Structure definition for exception and error handling
BEGIN TRANSACTION	Mark a block of statements as part of an explicit transaction

Example of using IF ELSE in T-SQL:

IF DATENAME(weekday, GETDATE()) IN (N'Saturday',N'Sunday') SELECT 'It is a Weekend'; ELSE SELECT 'It is Weekday';

Comments

Comments are strings of descriptive text, also known as comments, in the program code that will be ignored by the compiler. Comments can be inserted within the source code of a statement, a block of code, or a store procedure. Comments explain the purpose of the program, special execution conditions, and provide revision history information…. Syntax:

-- Day la comment inline -- Day la comment inline /*day la comment khoi lenh*/

Batch Separators

Batch is a set of one or more T-SQL statements that are sent in a single application execution. The statements in T-SQL in a batch are repackaged into a unit of execution (one execution), called an execution plan. The process of executing the instruction sets inside the batch is called batch processing.

A batch seperator is controlled by SQL Server client tools such as SSMS to execute the command. For example, you define GO as a batch seperator in SSMS.

Example batch seperator:

USE AdventureWorks2019 SELECT * FROM HumanResources.Employee GO -- cau lenh tiep theo

Sets and Predicate Logic (sets and predicate logic )

Sets and Predicate Logic are 2 mathematical fundamentals used in SQL Server 2019. Both of these theories are used to query data in SQL Sever 2019

Set Theory (set theory)

Set theory is a mathematical foundation used in the relational database model. A set is a collection of distinct objects considered as a whole. For example, all the employees in an Employee table can be thought of as a set.

Set Theory Applications	Applications in SQL Server Queries
Act on the entire set at once	Query the whole table at once
Set-based handling and declaration	Use properties in SQL server to retrieve specific data
The elements in the set must be unique	Define unique key for table
No sorting instructions	The results of the query are not retrieved in any order

One of the set operators is the INTERSECT operator. It returns the distinct rows generated by both the left and right input query operators.

Example of using INTERSECT:

USE AdventureWorks2019 GO SELECT ProductID FROM Production.Product INTERSECT SELECT ProductID FROM Production.WorkOrder;

Predicate Logic (predicate logic)

See more logic predicate concept here: https://en.wikipedia.org/wiki/Logic_b%E1%BA%ADc_nh%E1%BA%A5t

Predicate logic is a mathematical framework consisting of logical tests that give a result. The result is always displayed as true or false. In T-SQL, expressions such as WHERE and CASE expressions are based on predicate logic. Predicate logic is also used in other situations in T-SQL

Some Predicate logic in T-SQL are as follows:

• enforce confidentiality of data using a CHECK constraint
• Flow control using the IF . statement
• Join tables using ON . filter
• Filter data in queries using WHERE and HAVING . clauses
• Provides conditional logic for CASE . expressions
• Define subquery

Logical order of operators in a SELECT . statement

Along with the syntax of the various SQL Server elements, SQL Server users must also know how the entire query is executed. This procedure is a logical process that breaks the query and executes the query in a predefined sequence. The SELECT statement is a query that will be used to explain the logical process of executing the query.

SELECT statement syntax:

SELECT <select list> FROM <table source> WHERE <search condition> GROUP BY <group by list> HAVING <search condition> ORDER BY <order by list>

Description of the elements in the SELECT statement:

Element	Describe
SELECT <select list>	Define the columns to be retrieved
FROM <table source>	Definition of queried table
WHERE <search condition>	Filter rows by predicate
GROUP BY <group by list>	Sort rows by group
HAVING <search condition>	Filter groups by predicate
ORDER BY <order by list>	Sort output.

Consider the example below:

USE AdventureWorks2019 SELECT SalesPersonID,YEAR(OrderDate) AS OrderYear FROM Sales.SalesOrderHeader WHERE CustomerID=30084 GROUP BY SalesPersonID, YEAR(OrderDate) HAVINg COUNT(*) > 1 ORDER BY SalesPersonID,OrderYear;

In the example above, the execution sequence of the SELECT statement is as follows:

1. The FROM clause is evaluated to determine the source table to be queried
2. The WHERE clause is evaluated to filter the rows in the source table, which is defined by the predicate mentioned after the WHERE clause.
3. Next, the GROUP BY clause is evaluated. This clause sorts the filtered data in the WHERE . clause
4. The HAVING clause is evaluated
5. The SELECT clause is executed to specify which columns should output the same query result
6. Finally, the ORDER BY statement is executed to display the output

USE AdventureWorks2019 -- thu tu thuc thi cua cau lenh select 5.SELECT SalesPersonID,YEAR(OrderDate) AS OrderYear FROM 1.Sales.SalesOrderHeader 2.WHERE CustomerID=30084 3.GROUP BY SalesPersonID, YEAR(OrderDate) 4.HAVINg COUNT(*) > 1 6.ORDER BY SalesPersonID,OrderYear;