T-SQL Basics

Transact-SQL (T-SQL) is Microsoft's proprietary extension to the SQL language that is used by Microsoft SQL Server. It includes procedural programming, local variables, and support for string, date and mathematical functions, among other capabilities.

Core Concepts

Statements: T-SQL commands that perform actions, such as retrieving data (SELECT), inserting data (INSERT), updating data (UPDATE), and deleting data (DELETE).
Data Types: Define the type of data that can be stored in a column (e.g., INT for integers, VARCHAR for strings, DATETIME for dates and times).
Variables: Used to store values that can be referenced and manipulated within a T-SQL batch or stored procedure. Declared using the DECLARE keyword.
Control Flow Language: Constructs like IF...ELSE, WHILE, and CASE that allow for conditional execution and looping.

Essential Statements

1. SELECT Statement

Used to retrieve data from one or more tables. The most fundamental T-SQL statement.


SELECT column1, column2, ...
FROM table_name
WHERE condition;

2. INSERT Statement

Used to add new rows of data into a table.


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

3. UPDATE Statement

Used to modify existing data in a table.


UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

4. DELETE Statement

Used to remove rows from a table.


DELETE FROM table_name
WHERE condition;

Important: Always use a WHERE clause with UPDATE and DELETE statements to avoid affecting all rows in the table.

Working with Data

Filtering Data (WHERE Clause)

The WHERE clause specifies criteria for selecting or modifying rows. It uses comparison operators (=, <>, >, <, >=, <=) and logical operators (AND, OR, NOT).


SELECT ProductName, Price
FROM Products
WHERE Category = 'Electronics' AND Price > 500;

Sorting Data (ORDER BY Clause)

The ORDER BY clause sorts the result set in ascending (ASC, default) or descending (DESC) order.


SELECT OrderID, OrderDate
FROM Orders
ORDER BY OrderDate DESC;

Introduction to Variables

Variables are declared using DECLARE and assigned values using SET or SELECT.


DECLARE @productCount INT;
SET @productCount = (SELECT COUNT(*) FROM Products);
PRINT 'Total products: ' + CAST(@productCount AS VARCHAR);

Tip: Use PRINT statements for debugging and displaying simple messages.

This section covers the fundamental building blocks of T-SQL. For more detailed information on specific statements, functions, and advanced concepts, please refer to the relevant sections in the documentation.