Anatomy of a Query

Transact-SQL, or T-SQL, as it has come to be commonly known, is the language that is used to communicate with Microsoft SQL Server. Any actions a user wishes to perform in a server, such as retrieving or modifying data in a database, creating objects, changing server configurations, and so on, are all done via a T-SQL command.

In this chapter, we will be introduced to the typical components of a T-SQL statement, including the logical order with which SQL Server processes a statement. This is essential for introducing the reader to why certain query writing patterns work best and to provide a fundamental reference for better understanding the other chapters.

There are four main groups of T-SQL statements that we can have in a Relational Database Management System (RDBMS) like SQL Server:

• Data Control Language statements, also known as DCL, are used to handle control access to a database or parts of the database. T-SQL commands such as GRANT and REVOKE are used to change permissions on objects (known as securables), or to add users to SQL Server.
• Transactional Control Language statements, also known as TCL, are used to control transactions in SQL Server with T-SQL commands such as BEGIN TRANSACTION, COMMIT TRANSACTION, or ROLLBACK.
• Data Definition Language statements, also known as DDL, are used to create, change, or delete the database and any objects contained within such as tables or indexes. Examples of DDL include CREATE, ALTER, CREATE OR ALTER, or DROP T-SQL commands.
• Data Manipulation Language statements, also known as DML, can be distilled into 4 logical operations on a database:
  • Retrieving data via the SELECT statement.
  • Updating and Inserting data, also known as UPSERTs, via the UPDATE and INSERT statements.
  • Deleting data via the DELETE statement.
  • There is also a MERGE statement. This is a conditional structure that combines UPDATEs, INSERTs and/or DELETEs into a single statement, which together with SELECTs, make up the fundamental DML operations available in SQL Server.

While all these types of statements must be parsed and validated by the Database Engine before execution, with very few exceptions only DML statements are optimized. This means that the way DML statements are constructed can have an impact on their resulting performance, so care must be taken to write them efficiently. For this reason, we will focus on DML statements throughout the course of this book.

In this chapter we will cover the following topics: 

• Building blocks of a T-SQL statement
• Logical statement-processing flow