T-SQL Statements

Transact-SQL (T-SQL) is Microsoft's proprietary extension of SQL used for the Microsoft SQL Server relational database management system. T-SQL extends SQL by adding procedural programming, local variables, various support functions for data manipulation, and other enhancements.

This section covers the various statements available in T-SQL, categorized by their primary function:

Data Definition Language (DDL) Statements

DDL statements are used to define and manage database objects.

CREATE: Used to create database objects like tables, views, indexes, stored procedures, and functions.
ALTER: Used to modify the structure of existing database objects.
DROP: Used to delete database objects.
TRUNCATE: Used to remove all rows from a table quickly.
RENAME: Used to rename a database object.

Data Manipulation Language (DML) Statements

DML statements are used to manage data within database objects.

SELECT: Retrieves data from one or more tables.
INSERT: Adds new rows of data into a table.
UPDATE: Modifies existing data in a table.
DELETE: Removes rows of data from a table.
MERGE: Performs INSERT, UPDATE, or DELETE operations on a target table based on the results of a join with a source table.

Data Control Language (DCL) Statements

DCL statements are used to control access to data and database objects.

GRANT: Grants permissions to users or roles.
REVOKE: Revokes permissions from users or roles.

Transaction Control Language (TCL) Statements

TCL statements manage transactions within the database.

BEGIN TRANSACTION: Starts a new transaction.
COMMIT TRANSACTION: Saves all changes made during the current transaction.
ROLLBACK TRANSACTION: Undoes all changes made during the current transaction.
SAVE TRANSACTION: Sets a point within a transaction that can be rolled back to.

Control Flow Statements

These statements control the flow of execution in T-SQL batches and stored procedures.

IF...ELSE: Executes a block of code conditionally.
WHILE: Repeats a block of code as long as a condition is true.
BREAK: Exits the innermost WHILE loop.
CONTINUE: Skips the rest of the current loop iteration and proceeds to the next.
GOTO: Transfers control to a labeled statement within the same batch or procedure.
WAITFOR: Suspends execution for a specified time or until a specific time.
TRY...CATCH: Implements error handling for T-SQL code blocks.

Example: Using IF...ELSE and SELECT

SQL Example

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'MyTable')
BEGIN
    PRINT 'Table MyTable already exists.'
END
ELSE
BEGIN
    CREATE TABLE MyTable (
        ID INT PRIMARY KEY,
        Name VARCHAR(100)
    );
    PRINT 'Table MyTable created successfully.'
END;