T-SQL Statements - SQL Reference

Data Definition Language (DDL) Statements

CREATE

Used to create objects in the database such as tables, views, indexes, stored procedures, functions, and triggers.

CREATE TABLE Customers (CustomerID INT PRIMARY KEY, CompanyName VARCHAR(255));

ALTER

Used to modify existing objects in the database. This can include adding, deleting, or modifying columns in a table, or changing constraints.

ALTER TABLE Customers ADD Email VARCHAR(255);

DROP

Used to delete objects from the database.

DROP TABLE Customers;

TRUNCATE

Removes all rows from a table quickly, but the table structure remains. It's generally faster than DELETE for large tables.

TRUNCATE TABLE Orders;

RENAME

Used to rename an existing database object.

RENAME OBJECT Employees TO Staff;

Data Manipulation Language (DML) Statements

SELECT

Retrieves data from one or more tables. This is the most common T-SQL statement.

SELECT CompanyName, Email FROM Customers WHERE CustomerID = 1;

INSERT

Adds new rows of data into a table.

INSERT INTO Customers (CustomerID, CompanyName, Email) VALUES (2, 'New Corp', 'contact@newcorp.com');

UPDATE

Modifies existing rows in a table.

UPDATE Customers SET Email = 'info@newcorp.com' WHERE CustomerID = 2;

DELETE

Removes rows from a table.

DELETE FROM Customers WHERE CustomerID = 2;

Transaction Control Language (TCL) Statements

BEGIN TRANSACTION

Marks the beginning of a transaction.

BEGIN TRANSACTION;

COMMIT TRANSACTION

Saves all changes made within the current transaction.

COMMIT TRANSACTION;

ROLLBACK TRANSACTION

Undoes all changes made within the current transaction.

ROLLBACK TRANSACTION;

SAVE TRANSACTION

Marks a point within a transaction to which you can later roll back.

SAVE TRANSACTION SavePoint1;

Control Flow Statements

IF...ELSE

Executes a block of statements conditionally.

IF EXISTS (SELECT * FROM Customers WHERE CustomerID = 1) SELECT 'Customer exists' ELSE SELECT 'Customer does not exist';

WHILE

Executes a block of statements repeatedly as long as a condition is true.

DECLARE @Counter INT = 1; WHILE @Counter <= 5 BEGIN PRINT @Counter; SET @Counter = @Counter + 1; END;

CASE

Evaluates a list of conditions and returns a value when the first condition is met.

SELECT ProductName, CASE WHEN Price > 100 THEN 'Expensive' ELSE 'Affordable' END AS PriceCategory FROM Products;

GOTO

Transfers control to a labeled statement within the same batch or stored procedure.

-- Example: Use with caution! START: PRINT 'Looping...'; GOTO START;

TRY...CATCH

Handles errors that occur during the execution of T-SQL statements.

BEGIN TRY INSERT INTO Orders (OrderID) VALUES (100); COMMIT TRANSACTION; END TRY BEGIN CATCH -- Handle error SELECT ERROR_MESSAGE(); ROLLBACK TRANSACTION; END CATCH;