T-SQL

Introduction

Welcome to this T-SQL tutorial, where we’ll take you from the foundations of SQL to modern Transact-SQL (T-SQL) used in Microsoft SQL Server. Whether you’re a beginner or looking to refine your skills, this guide will walk you through essential concepts, commands, and best practices.

We’ll start with the history and evolution of SQL, then move into installation, SQL Server Management Studio (SSMS) setup, and core T-SQL commands—covering DDL, DML, DQL, TCL, and DCL. You’ll also learn about indexing for performance optimization.

By the end, you’ll have a solid grasp of T-SQL to query, manipulate, and manage databases efficiently. Let’s get started!

Table of Contents

Foundations & Setup

• 001. History of SQL to Modern T-SQL
• 002. Getting Started
• 003. Installation
• 004. SQL Server Management Studio (SSMS) – Walkthrough

Core T-SQL Language

• 005. T-SQL DDL (Data Definition Language) Commands
• 006. T-SQL DML (Data Manipulation Language) Commands
• 007. T-SQL DQL (Data Query Language) Commands
• 008. T-SQL TCL (Transaction Control Language) Commands
• 009. T-SQL DCL (Data Control Language) Commands

Advanced Topics

Performance Optimization

• 010. Indexing in T-SQL
  • Clustered vs. Non-Clustered Indexes
  • Index Maintenance (Rebuild/Reorganize)
  • Covering Indexes and Included Columns
• 011. Query Performance Tuning
  • Execution Plans (Actual vs. Estimated)
  • Statistics and Cardinality Estimation
  • Parameter Sniffing and Optimization Hints

Data Integrity & Security

• 012. Constraints and Triggers
  • Advanced Constraint Types (CHECK, UNIQUE, FOREIGN KEY)
  • INSTEAD OF vs. AFTER Triggers
• 013. Row-Level Security (RLS) & Dynamic Data Masking
  • Implementing RLS with Security Policies
  • Masking Sensitive Data (Partial, Random, Default Masks)

Advanced Query Techniques

• 014. Window Functions & Advanced Aggregation
  • PARTITION BY, ORDER BY, and Framing (ROWS/RANGE)
  • LEAD(), LAG(), FIRST_VALUE(), and NTILE()
• 015. Common Table Expressions (CTEs) & Recursive Queries
  • Hierarchical Data (Employee/Org Charts)
  • Graph Processing with Recursive CTEs

Error Handling & Transactions

• 016. TRY-CATCH & Custom Error Handling
  • THROW vs. RAISERROR
  • Custom Error Logging
• 017. Transaction Isolation Levels
  • Read Uncommitted to Serializable
  • Deadlock Prevention Strategies

Temporal & Spatial Data

• 018. Temporal Tables (Time-Travel Queries)
  • System-Versioned Tables
  • Querying Historical Data with FOR SYSTEM_TIME
• 019. Spatial Data in T-SQL
  • GEOGRAPHY vs. GEOMETRY
  • Distance, Intersection, and Buffer Queries

Automation & Dynamic SQL

• 020. Dynamic SQL & Stored Procedures
  • Safe Execution with sp_executesql
  • SQL Injection Mitigation
• 021. SQL Agent Jobs & Automation
  • Scheduling Maintenance Tasks
  • Alerting and Notifications

Practice

• 022. T-SQL Practice Problems

Appendix

• 023. Frequently Asked Questions
• 024. Glossary of Key Terms

This site is open source. Improve this page.