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
SQL
The standard language for managing and querying relational databases
RDBMS
A database system that stores data in related tables, supporting ACID transactions and SQL queries
CRUD
The four basic operations for persistent storage in databases
DML
SQL commands used for querying and modifying data
DDL
SQL commands that define or modify database structures