Transact-SQL (T-SQL) Reference

Transact-SQL (T-SQL) is Microsoft's proprietary extension to SQL (Structured Query Language) used by Microsoft SQL Server. T-SQL extends SQL by adding procedural programming, local variables, various support functions for data manipulation, and enhancements for creating and managing database objects.

Key T-SQL Concepts

Data Types

T-SQL supports a rich set of data types for storing various kinds of information, including numeric, character string, date and time, binary, and spatial data. Understanding data types is crucial for efficient data storage and manipulation.

  • Numeric Types: INT, BIGINT, DECIMAL, FLOAT, MONEY
  • Character String Types: VARCHAR, NVARCHAR, CHAR, NCHAR
  • Date and Time Types: DATE, TIME, DATETIME2, DATETIMEOFFSET
  • Binary Types: VARBINARY, BINARY, IMAGE (deprecated)
  • Other Types: BIT, UNIQUEIDENTIFIER, XML, GEOMETRY, GEOGRAPHY

Statements and Commands

T-SQL provides a comprehensive set of statements for managing and querying data, as well as defining database structures.

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.

Data Definition Language (DDL)

  • CREATE TABLE: Defines a new table.
  • ALTER TABLE: Modifies an existing table.
  • DROP TABLE: Deletes a table.
  • CREATE INDEX: Creates an index for a table.
  • CREATE VIEW: Defines a virtual table.
  • CREATE PROCEDURE: Creates a stored procedure.
  • CREATE FUNCTION: Creates a user-defined function.

Control-Flow Language

  • IF...ELSE: Executes statements conditionally.
  • WHILE: Repeats statements while a condition is true.
  • BEGIN...END: Groups statements.
  • TRY...CATCH: Handles errors.

Built-in Functions

T-SQL offers a wide array of built-in functions for performing calculations, string manipulations, date and time operations, and more. Here are a few examples:

  • String Functions: LEN(), SUBSTRING(), REPLACE(), UPPER(), LOWER()
  • Numeric Functions: ABS(), ROUND(), CEILING(), FLOOR()
  • Date Functions: GETDATE(), DATEPART(), DATEADD(), DATEDIFF()
  • Aggregate Functions: COUNT(), SUM(), AVG(), MAX(), MIN()

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, modularity, and security.


-- Example of a simple stored procedure
CREATE PROCEDURE GetCustomerOrders (@CustomerID INT)
AS
BEGIN
    SELECT
        o.OrderID,
        o.OrderDate,
        c.CompanyName
    FROM
        Orders AS o
    JOIN
        Customers AS c ON o.CustomerID = c.CustomerID
    WHERE
        o.CustomerID = @CustomerID;
END;
GO

-- Executing the stored procedure
EXEC GetCustomerOrders @CustomerID = 10;
GO
Note: Always ensure your T-SQL code is well-commented and follows best practices for readability and maintainability.

Transactions

Transactions allow you to group a series of database operations into a single logical unit of work. They ensure data integrity by providing Atomicity, Consistency, Isolation, and Durability (ACID properties).


BEGIN TRANSACTION;
BEGIN TRY
    -- Perform operations here
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

    -- If all operations are successful, commit the transaction
    COMMIT TRANSACTION;
    PRINT 'Transaction committed successfully.';
END TRY
BEGIN CATCH
    -- If any error occurs, roll back the transaction
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    PRINT 'Transaction rolled back due to an error.';
    -- Optionally, log the error details using ERROR_MESSAGE(), ERROR_NUMBER(), etc.
END CATCH;
GO

Error Handling

Robust error handling is crucial for reliable T-SQL applications. The TRY...CATCH block is the standard way to handle runtime errors.

Performance Tuning Considerations

Optimizing T-SQL queries is essential for efficient database performance. This includes proper indexing, query plan analysis, and avoiding common performance pitfalls.

Further Resources

↑