T-SQL Fundamentals

Documentation > SQL Server > T-SQL Fundamentals

Introduction to T-SQL

Transact-SQL (T-SQL) is Microsoft's procedural extension to SQL (Structured Query Language). It is used to interact with Microsoft SQL Server. T-SQL enhances SQL by adding procedural programming elements such as variables, control-flow statements, and error handling.

This document covers the fundamental concepts of T-SQL, providing a foundation for writing efficient and powerful queries and scripts for SQL Server.

Basic SQL Statements in T-SQL

T-SQL supports standard SQL data manipulation language (DML) and data definition language (DDL) statements.

Data Query Language (DQL)

• SELECT: Retrieves data from one or more tables.

  SELECT column1, column2
  FROM table_name
  WHERE condition;

Data Manipulation Language (DML)

• INSERT: Adds new rows of data to a table.

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

• UPDATE: Modifies existing data in a table.

  UPDATE table_name
  SET column1 = new_value
  WHERE condition;

• DELETE: Removes rows from a table.

  DELETE FROM table_name
  WHERE condition;

Data Definition Language (DDL)

• CREATE: Creates new database objects like tables, views, and stored procedures.

  CREATE TABLE new_table (
      column1 datatype,
      column2 datatype
  );

• ALTER: Modifies existing database objects.

  ALTER TABLE table_name
  ADD column3 datatype;

• DROP: Deletes database objects.

  DROP TABLE table_name;

Common T-SQL Data Types

Understanding data types is crucial for efficient data storage and manipulation. Here are some of the most common ones:

Operators in T-SQL

T-SQL uses various operators for comparisons, arithmetic, and logical operations.

• Arithmetic Operators: +, -, *, /, % (modulo)
• Comparison Operators: =, <, >, <=, >=, <> (not equal), != (not equal)
• Logical Operators: AND, OR, NOT
• String Concatenation: +
• Assignment Operator: = (used with DECLARE)

-- Example using operators
DECLARE @num1 INT = 10;
DECLARE @num2 INT = 5;
DECLARE @sum INT;
DECLARE @message VARCHAR(50);

SET @sum = @num1 + @num2; -- Arithmetic
SET @message = 'Result is: ' + CAST(@sum AS VARCHAR(10)); -- String concatenation

SELECT @num1 > @num2; -- Comparison (returns 1 for true)
SELECT @num1 <> @num2 OR @num1 = 10; -- Logical

Control Flow Statements

Control flow statements allow you to add logic and decision-making to your T-SQL scripts.

• IF...ELSE: Executes a block of code based on a condition.

  IF condition
  BEGIN
      -- statements if true
  END
  ELSE
  BEGIN
      -- statements if false
  END;

• WHILE: Repeats a block of code as long as a condition is true.

  WHILE condition
  BEGIN
      -- statements to repeat
      -- update condition variables
  END;

• CASE: Evaluates a list of conditions and returns one of multiple possible result expressions.

  CASE
      WHEN condition1 THEN result1
      WHEN condition2 THEN result2
      ELSE else_result
  END;

• GOTO: Transfers control to a labeled statement (use sparingly).
• WAITFOR: Delays execution.

Built-in Functions

SQL Server provides a rich set of built-in functions for various purposes:

• String Functions: LEN(), SUBSTRING(), LEFT(), RIGHT(), REPLACE(), UPPER(), LOWER()
• Numeric Functions: ABS(), CEILING(), FLOOR(), ROUND(), RAND()
• Date and Time Functions: GETDATE(), DATEADD(), DATEDIFF(), YEAR(), MONTH(), DAY()
• Aggregate Functions: COUNT(), SUM(), AVG(), MIN(), MAX()
• System Functions: DB_NAME(), HOST_NAME()
• Conversion Functions: CAST(), CONVERT()

-- Example using functions
DECLARE @current_date DATE = GETDATE();
DECLARE @tomorrow DATE = DATEADD(day, 1, @current_date);

SELECT COUNT(*) AS TotalCustomers
FROM Customers
WHERE City = 'London';

Stored Procedures

Stored procedures are precompiled SQL statements stored in the database. They offer benefits like improved performance, reusability, and enhanced security.

-- Creating a stored procedure
CREATE PROCEDURE GetCustomerByID
    @CustomerID INT
AS
BEGIN
    SELECT CustomerName, Email
    FROM Customers
    WHERE CustomerID = @CustomerID;
END;
GO

-- Executing a stored procedure
EXEC GetCustomerByID @CustomerID = 101;

Views

Views are virtual tables based on the result-set of an SQL statement. They can simplify complex queries and provide a security layer.

-- Creating a view
CREATE VIEW ActiveCustomers AS
SELECT CustomerID, CustomerName, Email
FROM Customers
WHERE IsActive = 1;
GO

-- Querying a view
SELECT CustomerName, Email
FROM ActiveCustomers;

Triggers

Triggers are special stored procedures that automatically execute or fire when an event (like an INSERT, UPDATE, or DELETE statement) occurs on a table. They are often used for enforcing business rules or maintaining data integrity.

-- Example of an AFTER INSERT trigger
CREATE TRIGGER trg_AuditInsert
ON Orders
AFTER INSERT
AS
BEGIN
    INSERT INTO OrderAudit (OrderID, Action, AuditDate)
    SELECT OrderID, 'INSERT', GETDATE()
    FROM INSERTED; -- The 'inserted' virtual table contains the new rows
END;
GO

This covers the essential T-SQL fundamentals. For more advanced topics, refer to specific documentation on functions, error handling, transactions, and more.