Introduction to Microsoft SQL Server Transact-SQL (T-SQL)

Microsoft SQL Server Transact-SQL (T-SQL) is a proprietary extension of SQL (Structured Query Language) used by Microsoft SQL Server and Sybase ASE (Adaptive Server Enterprise). T-SQL is a powerful and versatile language that provides procedural programming capabilities and standard SQL functionality found in all RDBMS (Relational Database Management System) products.

RDBMS is a collection of programs and capabilities that enable IT teams, database administrators (DBA), and database programmers to create, update, administer and interact with a relational database. A relational database is a database that uses tables to organize data into rows and columns that contain values that are related to each other.

Microsoft SQL Server is one of many RDBMS solutions available to organizations, so let’s begin by reviewing some of the characteristics of Microsoft SQL Server T-SQL.

Features and Benefits of T-SQL

Although there could be a lengthier list of features and benefits, I am going to introduce and describe several of the primary T-SQL features and benefits.

• Database development: T-SQL is used for creating and modifying database schema objects such as tables, views, indexes, and constraints.
• Automation: T-SQL scripts can automate repetitive tasks such as data migration, data cleansing, and scheduled maintenance activities.
• Procedural constructs: T-SQL supports procedural programming constructs like variables, control-of-flow language (IF…ELSE, WHILE, etc.), error handling with TRY…CATCH, and more. This allows for complex logic and automation within SQL scripts.
• Stored procedures, functions, and triggers: T-SQL enables the creation of stored procedures, user-defined functions, and triggers, which can encapsulate business logic and improve code reuse, maintainability, and security.
• Transaction control: T-SQL provides commands for explicit transaction management (BEGIN TRANSACTION, COMMIT, ROLLBACK), ensuring data integrity and consistency in multi-step operations.
• Rich data manipulation: Alongside standard SQL operations (SELECT, INSERT, UPDATE, DELETE), T-SQL offers additional capabilities like common table expressions (CTEs), ranking functions, pivoting/unpivoting data, and more.
• Enhanced security: T-SQL supports fine-grained security features such as row-level security, dynamic data masking, and encryption, helping organizations comply with security and privacy regulations.
• Reporting and analytics: T-SQL queries are used to extract and aggregate data for reporting and analytics purposes, providing insights into business operations.
• Integration: T-SQL can be integrated with other technologies such as .NET applications, PowerShell scripts, and ETL (Extract, Transform, Load) processes to streamline data workflows.
• Optimization and performance: T-SQL provides tools and techniques for query optimization, indexing, and performance tuning, ensuring efficient data retrieval and manipulation.

Understanding Relational Database Tables

A database table is similar to a spreadsheet in that it has rows, and each row has table-related columns with values stored in those columns as shown in Table 1.

Table 1: Rows and columns sample

Table 2 includes some sample employee data you may find in a database employee table with the first row containing the column names and the remaining four rows containing sample data you may find in the table columns.

Table 2: Sample employee information table

Database tables can contain columns that have T-SQL keys assigned to them to help manage and access the content in your database tables. I’ll introduce the two most common types of keys that are used to “link” data together that are stored in different tables.

• Primary key: An identifier for each record in a table. It ensures data uniqueness and serves as a reference for establishing table-to-table relationships.
• Foreign keys: Establishes a link between two tables, based on a standard column. It maintains referential integrity and enforces relationships between two tables.

To help you understand the SQL table format and how these two T-SQL key types can be used to link two tables, I have provided two sample tables below. The first table is used to store and retrieve information about your company employees. The second table is used to store and retrieve information about your company departments. Let’s explore both tables and discuss how to link them to retrieve information from both tables.

Table 3 contains a header row with the Employee_Info_Table column names and ten additional rows with columns containing values for that specific column. This table has the Employee_Num identified as the primary key (PK), and the Dept_Num identified as a foreign key (FK). This configuration is used to link the Dept_Num (FK) from the Employee_Info_Table to the Dept_Num (PK) in the Department_Info_Table.

Table 3: Employee_Info_Table

Table 4 contains a header row with the department information table column names, and six additional rows with values related to the data stored in those columns.

Table 4: Department_Info_Table

Multi-table Linked SQL Keys Explanation

The Employee_Info_Table contains a row with Employee_Num 62771 who is an employee who works in department number 82716. However, notice in table 1 that you don’t know what department that is, or where it is geographically located. By using a foreign key and primary key you can create a link from the Employee_Info_Table to the Department_Info_Table using the employee table column Dept_Num (FK) to the department table column Dept_Num (PK), allowing you to retrieve additional department-specific information.

Now that you have an understanding of relational databases, table formats, and how to link multiple tables together, I want to expand on the common T-SQL commands used when managing database tables and provide examples of these commands using the tables I introduced earlier.

Sample T-SQL Commands for Retrieving Data

SELECT: The SELECT statement is used to retrieve rows of data from one or more tables.

SELECT column1, column2
FROM table_name;

The following T-SQL command will return all rows and all columns located in the Department_Info_Table:

SELECT *
FROM Department_Info_Table;

The following T-SQL command will return the department name and region for all rows located in the Department_Info_Table:

SELECT DepartmentName, Region
FROM Department_Info_Table;

WHERE: The WHERE clause is used to specify a condition while fetching or modifying data.

SELECT column1, column2
FROM table_name
WHERE condition;

The following T-SQL command will return all columns in any row in a specific Region located in the Department_Info_Table:

SELECT *
FROM Department_Info_Table
WHERE Region = “Europe”;

The following T-SQL command will return the department name and region for any row with the region of “Europe” located in the Department_Info_Table:

SELECT DepartmentName, Region
FROM Department_Info_Table
WHERE Region = “Europe”;

ORDER BY: The ORDER BY clause is used to sort the result set in ascending or descending order.

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC; -- ASC for ascending, DESC for descending

The following T-SQL command will return the department number, department name, and region for any row with the region of “Europe” located in the Department_Info_Table and sort them in descending order by region:

SELECT Dept_Num, DepartmentName, Region
FROM Department_Info_Table
WHERE Region = “Europe”
ORDER BY DepartmentName DESC;

GROUP BY: The GROUP BY clause is used to group rows that have the same values into summary rows.

SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1;

The following T-SQL command will return a row for each different Region and the number of departments in each region, and sorted by Region in ascending order (alphabetically):

SELECT Region, COUNT(*)
FROM Department_Info_Table
GROUP BY Region
ORDER BY Region;

Sample T-SQL Commands for Managing Data

INSERT INTO: The INSERT INTO statement is used to add new rows of data into a table.

INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

The following T-SQL command will insert a new row into the Department_Info_Table using the values specified for each column. The Dept_Num (PK) will be auto-incremented and generated. If a value is not specified for any of the remaining columns, it will populate the optional default value, but if no default value, it will leave it blank:

INSERT INTO Department_Info_Column (DepartmentName, Region)
VALUES (“Sales”, “Europe”);

Results from INSERT INTO:

UPDATE: The UPDATE statement is used to modify existing data in a table.

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

The following T-SQL command will modify the blank Department_HQ for the row we just added by locating the Dept_Num with value of 82721 and populating the Department_HQ column with value specified:

UPDATE Department_Info_Table
SET Department_HQ = “Florence”
WHERE Dept_Num = 82721

Results from UPDATE:

DELETE: The DELETE statement is used to remove rows from a table.

DELETE FROM table_name
WHERE condition;

The following T-SQL command will delete the row we just added using the INSERT INTO statement, and then modified using the UPDATE statement, by finding the department number specified in the DELETE command and removing it from the table:

DELETE FROM Department_Info_Table
WHERE Dept_Num = 82721

Sample T-SQL Commands for Managing Tables

CREATE TABLE: The CREATE TABLE statement is used to create a new table.

CREATE TABLE table_name (
     column1 datatype,
     column2 datatype,
     ...
);

The following T-SQL command will create a new table called Job_Positions, that will contain four fields used to store information about employee job positions. The first column will be an auto-incremented, auto-generated integer field, second column will be a variable character column allowing up to 30 characters, the third column is for minimum salary with 8 positions, 2 of them being to the right of the decimal point:

CREATE TABLE Job_Positions (
     JobPosition_Num int,
      JobTitle varchar2(30),
     MinSalary decimal(8,2),
);

Results from CREATE TABLE statement:

ALTER TABLE: The ALTER TABLE statement is used to add, modify, or drop columns in an existing table.

ALTER TABLE table_name
ADD column_name datatype;
ALTER TABLE table_name
DROP COLUMN column_name;

The following T-SQL command will modify the table we just created, by adding a MaxSalary column using a Decimal data type:

ALTER TABLE Job_Positions
ADD MaxSalary decimal(8,2);

Results from ALTER TABLE statement:

DROP TABLE: The DROP TABLE statement is used to delete an existing table.

DROP TABLE table_name;
```

The following T-SQL command will delete the table we just created:

DROP TABLE Job_Positions;

Practice These T-SQL Commands

These are some of the fundamental T-SQL commands you’ll encounter frequently. Practice with them in a SQL Server environment and you’ll gradually become more proficient with them and learn additional T-SQL commands to successfully configure and manage your SQL Server relational database tables.