Introduction to Transact-SQL (T-SQL)
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:
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 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];
CREATE TYPE usertype from varchar(20) NOT NULL
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 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:
|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 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:
|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:
|2||*, / , %|
|3||+ , –|
|4||= , < , > , >= , <= , != , !>|
|7||BETWEEN, IN , CONTAINS, LIKE, OR|
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
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:
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.
T-SQL provides a set of functions to support summarizing large volumes of data e.g. SUM, MIN, MAX, AVG, COUNT, COUNTBIG…
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.
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…
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 ‘@’.
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.
SELECT @@LANGUAGE as 'Language'
They will return the language used by SQL Server
Some of the functions that can be encountered:
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
|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 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 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:
|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:
- The FROM clause is evaluated to determine the source table to be queried
- The WHERE clause is evaluated to filter the rows in the source table, which is defined by the predicate mentioned after the WHERE clause.
- Next, the GROUP BY clause is evaluated. This clause sorts the filtered data in the WHERE . clause
- The HAVING clause is evaluated
- The SELECT clause is executed to specify which columns should output the same query result
- 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;