SQL Language Reference

Introduction to Transact-SQL

Transact-SQL (T-SQL) is Microsoft's proprietary extension of SQL (Structured Query Language) that is used for managing and manipulating databases in Microsoft SQL Server. T-SQL extends SQL by adding procedural programming, local variables, string and date processing functions, scientific functions, and other enhancements.

Key features of T-SQL include:

  • Procedural programming constructs (IF, WHILE, GOTO)
  • User-defined functions and stored procedures
  • Error handling with TRY...CATCH blocks
  • Transaction control statements

Data Types

SQL Server supports a wide range of data types to store various kinds of information. Understanding these is crucial for efficient database design and querying.

Common Data Types:

  • INT, BIGINT, SMALLINT, TINYINT: Integer types.
  • DECIMAL, NUMERIC: Exact numeric types with fixed precision and scale.
  • FLOAT, REAL: Approximate numeric types.
  • VARCHAR, NVARCHAR: Variable-length character strings.
  • CHAR, NCHAR: Fixed-length character strings.
  • DATE, TIME, DATETIME2, DATETIME: Date and time types.
  • BIT: Boolean type (0, 1, or NULL).
  • UNIQUEIDENTIFIER: Globally unique identifier (GUID).

For a complete list and detailed descriptions, refer to the official data types documentation.

Core SQL Statements

This section covers the fundamental SQL commands used for data manipulation and definition.

Data Manipulation Language (DML)

  • SELECT: Retrieves data from one or more tables.
  • INSERT: Adds new rows of data to a table.
  • UPDATE: Modifies existing data in a table.
  • DELETE: Removes rows of data from a table.
  • MERGE: Combines INSERT and UPDATE operations.

Data Definition Language (DDL)

  • CREATE TABLE: Creates a new table.
  • ALTER TABLE: Modifies an existing table structure.
  • DROP TABLE: Deletes a table.
  • CREATE INDEX: Creates an index on a table column.
  • CREATE VIEW: Creates a virtual table.

Example SELECT Statement:

SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE Country = 'Germany'
ORDER BY CompanyName;

Stored Procedures and Functions

Stored procedures and functions are precompiled sets of T-SQL statements that can be executed as a single unit. They improve performance, security, and code reusability.

Stored Procedures:

Stored procedures can perform complex operations, accept input parameters, and return output parameters or result sets.

CREATE PROCEDURE GetCustomerOrders (@CustomerID INT)
AS
BEGIN
    SELECT OrderID, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerID = @CustomerID;
END;

-- Executing the stored procedure
EXEC GetCustomerOrders @CustomerID = 10;

Functions:

Functions return a single value (scalar function) or a table (table-valued function).

CREATE FUNCTION dbo.CalculateTotalOrderAmount (@OrderID INT)
RETURNS DECIMAL(10, 2)
AS
BEGIN
    DECLARE @Total DECIMAL(10, 2);
    SELECT @Total = SUM(UnitPrice * Quantity)
    FROM [Order Details]
    WHERE OrderID = @OrderID;
    RETURN @Total;
END;

-- Using the scalar function
SELECT dbo.CalculateTotalOrderAmount(10248) AS OrderTotal;

Advanced Topics

Explore more advanced features of T-SQL for complex data management scenarios.

  • Transactions and Concurrency Control
  • Error Handling and Debugging
  • Indexing Strategies
  • Query Optimization
  • Common Table Expressions (CTEs)
  • Window Functions
  • JSON and XML Support

Dive deeper into these topics in the advanced SQL topics section.