Back to Data Glossary

What is T-SQL

Microsoft's extension of SQL for SQL Server, providing additional features like stored procedures, built-in functions, and advanced transaction control.

How It Works

T-SQL (Transact-SQL) extends standard SQL with programming features like variables, flow control, error handling, and transaction management. It's the primary way to interact with Microsoft SQL Server, allowing developers to write complex stored procedures, functions, and triggers that run directly on the database server.

Technical Details

T-SQL adds many proprietary extensions to standard SQL, including local variables, string processing, date processing, mathematics functions, and changes to DELETE and UPDATE statements. It's particularly powerful for writing stored procedures that encapsulate business logic within the database.

How to Write It

Basic Syntax

-- T-SQL specific features:
-- 1. Variables and flow control
DECLARE @counter INT = 1;
DECLARE @result VARCHAR(100);

IF @counter > 0
BEGIN
    SET @result = 'Positive';
END
ELSE
BEGIN
    SET @result = 'Zero or negative';
END;

-- 2. Stored procedure with output parameter
CREATE PROCEDURE CalculateOrderStats
    @customerId INT,
    @totalOrders INT OUTPUT,
    @totalValue MONEY OUTPUT
AS
BEGIN
    SELECT 
        @totalOrders = COUNT(*),
        @totalValue = SUM(total_amount)
    FROM orders
    WHERE customer_id = @customerId;
END;

-- 3. Common Table Expression (CTE) with recursion
WITH EmployeeHierarchy AS (
    -- Anchor member
    SELECT 
        employee_id,
        manager_id,
        full_name,
        1 as level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive member
    SELECT 
        e.employee_id,
        e.manager_id,
        e.full_name,
        eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh 
        ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

Supported Platforms

Learn More

Best Practices

  • Use stored procedures to encapsulate complex business logic
  • Implement proper error handling with TRY/CATCH blocks
  • Use transactions for maintaining data consistency
  • Parameterize queries to prevent SQL injection
  • Use appropriate variable types and sizes to optimize performance

Common Pitfalls

  • Not handling transactions properly in error scenarios
  • Overusing cursors instead of set-based operations
  • Not considering the scope of variables in stored procedures
  • Failing to properly test for NULL values
  • Writing non-reentrant stored procedures

Advanced Tips

  • Use table variables for better performance with small datasets
  • Leverage CTEs for recursive queries and code readability
  • Implement proper indexing strategies for stored procedures
  • Use OUTPUT parameters instead of SELECT statements for returning values
  • Consider using MERGE statements for upsert operations

Related Terms

Space, at your fingertips
astronaut

What will you discover?