Introduction to T-SQL

Overview

To interact with databases hosted on Microsoft SQL Server (MS SQL), we use Transact-SQL (T-SQL), which is Microsoft’s implementation of the SQL standard. The DBMS runs T-SQL queries. The client (for example, an external program) sends a request in T-SQL using a special API. MS SQL Server executes the request and then sends the result to the client, as shown in the following diagram.

The popularity of the MS SQL Server paved the way for the popularity of T-SQL and because MS SQL Server is used in many large enterprises, it is useful to learn T-SQL.

T-SQL can be used to query data for analysis purposes, and is also a valuable instrument in a data analyst’s toolbox. Additionally, T-SQL is a procedural programming language, which makes it possible for developers to create business logic on top of MS SQL Server databases.

SQL vs. T-SQL

It is important that we distinguish between Structured Query Language (SQL) and Transact-SQL (T-SQL). This table summarizes the distinctions.

In other words, T-SQL has derived some things from SQL and introduced new functionality.

Types of T-SQL commands

Depending on the task that a T-SQL command is trying to accomplish, it can be related to one of the following categories:

• Data definition language (DDL)
• Data manipulation language (DML)
• Data control language (DCL)

Data definition language

T-SQL can be used to create, alter, and delete various database objects, like the databases themselves, tables, stored procedures, views, and so on. A database object is an entity that can store, reference, or work with data. These commands are part of DDL:

• CREATE: Used for creating new database objects.

• ALTER: Used for modifying existing database objects.

• DROP: Used for deleting database objects.

• TRUNCATE: Used for deleting data from database objects.

Data manipulation language

We can use T-SQL to work with data. These commands are part of DML:

• SELECT: Used to extract data from tables.

• INSERT: Used to insert data into tables.

• UPDATE: Allows us to edit the existing data.

• DELETE: Used to delete data from tables.

Data control language

T-SQL also allows us to control what actions can be performed against the database by specific users. It is possible to configure access on the database object level. For example, some users might only access the database in a read-only mode, meaning they could extract data but can’t edit it or insert new information. The DCL part of T-SQL is not within the scope of this course, because software engineers rarely have to work with the security policies of databases. That is a job of database administrators.