Getting Started with Transact-SQL (T-SQL)

Welcome to the foundational guide for Transact-SQL (T-SQL), Microsoft's proprietary extension to SQL. T-SQL is the primary language for interacting with Microsoft SQL Server and Azure SQL Database.

What is T-SQL?

T-SQL enhances standard SQL with procedural programming constructs, local variables, various support functions for string processing, date processing, mathematical functions, and other enhancements.

Key Components

  • Data Definition Language (DDL): Used to define and modify database structures (e.g., CREATE TABLE, ALTER TABLE, DROP TABLE).
  • Data Manipulation Language (DML): Used to manage data within schema objects (e.g., INSERT, UPDATE, DELETE, SELECT).
  • Data Control Language (DCL): Used to control access to data and database objects (e.g., GRANT, REVOKE).

Your First T-SQL Query

Let's start with a simple query to retrieve data from a hypothetical Products table.


SELECT ProductID, ProductName, ListPrice
FROM Production.Product
WHERE ListPrice > 50.00
ORDER BY ProductName;

In this example:

  • SELECT ProductID, ProductName, ListPrice specifies the columns you want to retrieve.
  • FROM Production.Product indicates the table to query.
  • WHERE ListPrice > 50.00 filters the results to include only products with a list price greater than $50.00.
  • ORDER BY ProductName sorts the results alphabetically by product name.
Tip: Always terminate your SQL statements with a semicolon (;), although it's optional in some contexts. It's good practice for clarity and compatibility.

Common T-SQL Statements

Creating a Table (DDL)


CREATE TABLE dbo.Customers (
    CustomerID INT PRIMARY KEY IDENTITY(1,1),
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    CreatedDate DATETIME DEFAULT GETDATE()
);

Inserting Data (DML)


INSERT INTO dbo.Customers (FirstName, LastName, Email)
VALUES ('John', 'Doe', 'john.doe@example.com');

Updating Data (DML)


UPDATE dbo.Customers
SET Email = 'john.a.doe@example.com'
WHERE CustomerID = 1;

Deleting Data (DML)


DELETE FROM dbo.Customers
WHERE CustomerID = 1;

Next Steps

This section provides a brief introduction. Explore the following sections to delve deeper into specific T-SQL concepts like data types, operators, and various SQL statements.